How to expose the record count of a table in a separate scorecard?

[brunovaletta_mv]

Hello!

I have a data source with orders, and each orderID can be fulfilled by multiple Sellers, so I have a table that has no unique identifier, and an orderID can appear in multiple rows matching multiple sellers, like this:

orderID	Seller
001	A
001	B
002	A
003	A
003	B
003	C

I want to count how many orderIDs have more than 1 seller. So the only way I could achieve that was by using orderID as dimension and count_unique(seller) as the metric in a table, and filtering by count_unique(seller)>1.

Now I have a table like this:

orderID	count_unique(seller)
001	2
003	3

orderID 002 is not showing because of the filter, and the number I want is the record count of this table, i.e. 2 (2 orderIDs with count_unique(seller)>1).

But the only place this number is diplayed is in the footer of the table, and I can’t “extract” it to manipulate, like showing it in a scorecard or dividing it by total amount of orders.

Is there a way to achieve that? Maybe there is some other way to get that number other than using this table, but if I could simply “extract” the record count from the table into a scorecard, that would do the job as well.

[sam8]

So the key here is that you need to create a “table” of just the orders with unique sellers, and then you can get the record count of that table.

One way to create that table is to use a blend without a join. You can start a blend with the dimension OrderID and the metric UniqueSellers, along with your filter for more than one unique seller. You don’t need to specify another table or a join.

Next, on your new blended table, you can create a new calculated field like “COUNT(orderID)”. This is basically a record count for your new table, since orderID is the primary key for that table.

Finally, make a scorecard for that new count and you should be good to go!