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.
Hi,
I have fields Population across Various Months, Country, Segment, Channel etc.
Country | Month | Segment | Population |
IN | 2023-M04 | A | 80 |
IN | 2023-M05 | A | 90 |
IN | 2023-M06 | A | 100 |
US | 2023-M04 | A | 80 |
US | 2023-M05 | A | 90 |
US | 2023-M06 | A | 100 |
Now when I use a KPI card to display the Population and if Month 06 and Ctry "IN" is selected it should display - 100, when 3 months are selected it should average and display "90". And now when both "IN" and "US" are selected it should add up the averages of both the countries individually and display "180" .
Please help.
Solved! Go to Solution.
Hi @prasanna11289 ,
Please try this measure:
Measure :=
VAR _country = SELECTEDVALUE('Table'[Country])
VAR _value1 = AVERAGEX(FILTER('Table','Table'[Country]=MAX('Table'[Country])),'Table'[Population])
VAR _value2 = SUMX(VALUES('Table'[Country]),AVERAGE('Table'[Population]))
VAR _result = IF(_country<>BLANK(),_value1,_value2)
RETURN
_result
The PBIX file is attached for reference:
Best Regards,
changqing
Hi @prasanna11289 ,
Please try this measure:
Measure :=
VAR _country = SELECTEDVALUE('Table'[Country])
VAR _value1 = AVERAGEX(FILTER('Table','Table'[Country]=MAX('Table'[Country])),'Table'[Population])
VAR _value2 = SUMX(VALUES('Table'[Country]),AVERAGE('Table'[Population]))
VAR _result = IF(_country<>BLANK(),_value1,_value2)
RETURN
_result
The PBIX file is attached for reference:
Best Regards,
changqing
@Greg_Deckler - Thanks for the quick solution. I'm confused on one part. What are the variables I need to pass in Table[Group]? Could you please let me based on the fields in the original question?
@prasanna11289 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |