Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
prasanna11289
Regular Visitor

Find Sum of Average of Field

Hi,

 

I have fields Population across Various Months, Country, Segment, Channel etc.

CountryMonthSegmentPopulation
IN2023-M04A80
IN2023-M05A90
IN2023-M06A100
US2023-M04A80
US2023-M05A90
US2023-M06A100

 

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. 

1 ACCEPTED SOLUTION
changqing
Resolver II
Resolver II

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

changqing_0-1660197021022.png

The PBIX file is attached for reference:

PBIX file 

 

Best Regards,
changqing

View solution in original post

3 REPLIES 3
changqing
Resolver II
Resolver II

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

changqing_0-1660197021022.png

The PBIX file is attached for reference:

PBIX file 

 

Best Regards,
changqing

prasanna11289
Regular Visitor

@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?

Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.