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 All,
My dataset is in the below format -
I have slicers for level 3 region. Based on selected a region, I want to present how many level 2 regions' average is more than the level 3 region's average. For example, if I select the level 3 region as A, then I want to show - "<2> areas' average is more than the regional average." (because average of level 2 region a1 and a2 is more than the regional average of 11.7). I can represent the value <2> by using a card, but how do I calculate this final value?
Please help - Thank you for all your time and effort!
I couldn't find a solution to the problem. If anyone finds it, please share.
Solved! Go to Solution.
I am not sure this is what you're going for; maybe enough to get you started?
Measure = VAR lvl3GroupAvg = CALCULATE ( AVERAGE ( TableName[Value] ), ALLEXCEPT ( TableName, TableName[Level 3 Region] ) ) VAR lvl2GroupAvg = CALCULATE ( AVERAGE ( TableName[Value] ), ALLEXCEPT ( TableName, TableName[Level 2 Region] ) ) RETURN IF ( lvl2GroupAvg > lvl3GroupAvg, lvl2GroupAvg )
Proud to be a Super User!
Based on the table shown in the question, I want to do the following -
1. Show a message: "<x> number of level 2 regions have average more than the level 3 region"
2. Display a bar chart/table with details about only those level 2 regions whose average is more than the level 3 average
Hi @pbininja1
It would really help if you could create a table in eg. Excel and show the result you are expecting based on your data sample included in your original post.
I am not sure this is what you're going for; maybe enough to get you started?
Measure = VAR lvl3GroupAvg = CALCULATE ( AVERAGE ( TableName[Value] ), ALLEXCEPT ( TableName, TableName[Level 3 Region] ) ) VAR lvl2GroupAvg = CALCULATE ( AVERAGE ( TableName[Value] ), ALLEXCEPT ( TableName, TableName[Level 2 Region] ) ) RETURN IF ( lvl2GroupAvg > lvl3GroupAvg, lvl2GroupAvg )
Proud to be a Super User!
Thank you Chris!
@ChrisMendoza How do I calculate the count of the result of the above measure?
@pbininja1 wrote:@ChrisMendoza How do I calculate the count of the result of the above measure?
What are you trying to count?
Proud to be a Super User!
I am counting number of level 2 regions based on the result from the measure suggested by you.
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 |
---|---|
97 | |
96 | |
82 | |
74 | |
66 |
User | Count |
---|---|
121 | |
105 | |
102 | |
82 | |
72 |