How to group products by brand in each order to find an average brand per order

[Anonymous]

I have the transaction table that contains columns order_id, brand, SKU and quantity (see table below). I want to know the average brand per order id. For example, the table below show 3 order id (0001, 0002 and 0003), order id 0001 contains 3 brands (A, B and C), order id 0002 contains 2 brands and order id 0003 contains 1 brand. So, the average brand per order id should be (3+2+1)/3 = 2 brands per order id.

How can I write DAX to get this information? I have been stuck for several days 😞

Thanks in advance!

[Ashish_Mathur]

Hi,

Write these measures

Number of order ID = distinctcount(Data[Order_id])

Qty = sum(Data[Quantity])

Total = SUMX(VALUES(Data[Order_ID]),[Qty])

Avg = divide([Total],[Number of order ID])

Drag the Avg measure to a card visual.

Hope this helps.

[Anonymous]

Thank you Ashish. I think your solution give me an average quantity per order id, but what I'm looking for is average brand per order id.

[Ashish_Mathur]

You are welcome.  Try these

Number of order ID = distinctcount(Data[Order_id])

Number of brands = counta(Data[Brand])

Total = SUMX(VALUES(Data[Order_ID]),[Number of brands])

Avg = divide([Total],[Number of order ID])

Drag the Avg measure to a card visual.

[Anonymous]

I recieved a calculation error in measure "Number of brands" due to the function SUM cannot work with values of type String. 😞

[Ashish_Mathur]

Replace SUM() with COUNTA().

[Anonymous]

Unfortunately, it's not working. I try use the dummy table that I attached with this trend. Using your solution gave me 2.33 brands per order, but the right answer must be 2 brands per order.

[Ashish_Mathur]

Share the download link of the PBI file.

[Anonymous]

Here is the link.

Brand per Order 

Regards,

Krittapas T.

[Ashish_Mathur · Accepted solution]

Reivse the measure to

No of Brands = DISTINCTCOUNT(Sheet1[brand])

The answer will be 2.

Hope this helps.

[Ashish_Mathur]

Access denied message.

[Anonymous]

Sorry for that. I just changed access setting at the moment. You can open the link now. Thank you.