Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
227 | |
127 | |
118 | |
83 | |
78 |