Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |