Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |