Average number of product bought from a hierarchy level

[jameshoneywill]

Hi 

I have a sales table and a products table, joined on productID. 

There are productIDs for individual products but these then sit under several groupings, e.g. products apples, oranges, and pineapples are under Fruit, which in tern is under Food, also under food is Vegtables, which has individual products(and IDs) for carrorts and potatoes etc. etc. 

I want to do a unique count of how many food groups a customer buys.
i.e. 
if they buy apples oranges and carrots the answer is 2 NOT 3

But I can't work out how to do this without merging in the food group into the sales table via power query, which I don't want to do....

Surely there is a DAX measure which can count how many unique food groups a customer buys based on what ID's are in their sales table for that month.... I really can't get my head around how to do it!

TIA

[Ashish_Mathur]

HI,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[ArwaAldoud]

Hi @jameshoneywill try this measure below 

UniqueFoodGroupsBought =
CALCULATE(
DISTINCTCOUNT(Products[FoodGroup]),
SUMMARIZE(
Sales,
Sales[CustomerID],
Products[FoodGroup]
)
)

If this response was helpful, please accept it as a solution and give kudos to support other community members

[Deku · Accepted solution]

Countrows( Summarize( sales, product[category] ) )