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.
I have the following example table (JUst popped in a few examples
Date Category Value
01/01/2018 A 20
01/01/2018 A 23
01/01/2018 B 1
01/01/2018 C 1
01/01/2018 B 34
01/01/2018 D 2
etc
So I need to find the Average for each category and then just show the category with the Max Average. So this would be 2 measures. The Max average and the category with the Max Average I believe
Getting the average is easy. So for example
Date Category Avg Total Count of Category
01/01/2019 A 31.33 3
01/01/2019 B 1.00 1
01/01/2019 C 12.50 2
01/01/2019 D 24.00 1
So by the end of this I would want to be left with (I added a slicer on date)
Date Category Avg Total Count of Category
01/01/2019 A 31.33 3
Any help would be appreciated
Solved! Go to Solution.
One way could be to use a TOPN visual filter as follows
Hi @DebbieE
Try this:
1. Create this measure that uses the one ([Avg]) that you already have:
ShowMeasure = IF ( [Avg] = MAXX ( CALCULATETABLE ( DISTINCT ( Table1[Category] ); ALL ( Table1[Category] ) ); [Avg] ); 1 )
2. Place [ShowMeasure] in the visual level filters and select 'Show items when the value is' --> 1
I cant get this DAX working unfortunately. Should it contain ; ? It goes wrong when I get to ALL
I tried changing ; to , and I still get the syntax for AVG is incorrect (My AVG measure is working fine)
perfect thank you
One way could be to use a TOPN visual filter as follows
I was worried that would want it as a DaX query but fingers crossed they may be happy with that. Thank you so much. Ill just make sure that they are happy and mark as solved
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |