Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a visualization like this in Power BI Desktop:
I woud need to create a MEASURE that gives me the Average Number of Unique Categories sold by Partners in each month. It woud be shown like this:
1.5 = (Unique Categories Sold by Partner "X" (2) + Unique Categories Sold by Partner "Y" (1) / Number of Partners (2)
I would be very thankful if someone could hel me here.
Joaquin
Solved! Go to Solution.
Hi @johmedrano
Try this MEASURE
Measure = SUMX ( VALUES ( SalesTable[Partner ID] ), CALCULATE ( DISTINCTCOUNT ( SalesTable[Category] ) ) ) / DISTINCTCOUNT ( SalesTable[Partner ID] )
Hi @johmedrano
Try this MEASURE
Measure = SUMX ( VALUES ( SalesTable[Partner ID] ), CALCULATE ( DISTINCTCOUNT ( SalesTable[Category] ) ) ) / DISTINCTCOUNT ( SalesTable[Partner ID] )
Hello and thank you for your help here.
Could you kindly advise on the formula to use in case that the relationship between Products and Categories is not in the Sales Table but in a separate table. In the Sales table we would only see Products. Thanks !
Thanks a lot for your answer.
Your formula is giving me the result below (see table).
I would need that DistinctCount of Partners ID is based on each specific month and not on the entire Sales Table.
Do you possibly know how I can add that to your DAX formula ?
Sorry for my lack of knowledge here... I am really new in DAX.
Is the issue resolved?
Yes, the issue is solved. I used this formula:
Measure = SUMX ( VALUES ( SalesTable[Partner ID] ), CALCULATE ( DISTINCTCOUNT ( SalesTable[Category] ) ) ) / CALCULATE(DISTINCTCOUNT(SalesTable[Partner ID]),Filter(Sales,[Total Sales]>0))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
145 | |
108 | |
107 | |
90 | |
65 |