Ratio using distinctcount

[Pewima]

Hello,

I have a table looking as follows:

Country Item

France Widget

France Widget

France Stone

Belgium Widget

Belgium Widget

What I try to do, is to create a measure that I can filter per country, so that it calculates the number of distinct items (widget or stones) as ratio of the total number of distinct items in the table.

In other words, when I filter on France the result should be 1 (France has 2 distinct items, widget and stone, and the total distinct items in the column are widget and stone), and when I filter on Belgium the result should be 0,5.

Any ideas on how to do this?

Many thanks!
Mark

[Greg_Deckler · Accepted solution]

Item Ratio = VAR NumItems = DISTINCTCOUNT([Item])
VAR NumAllItems = CALCULATE(DISTINCTCOUNT([Item]),ALL(WidgetsNStones))
RETURN NumItems/NumAllItems