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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kjmts5200
Frequent Visitor

How to create measure to sum averages by client

Hello,

 

I am trying to create a measure that sums the averages by client over whatever period of time I chose.  I have a data table with 150k clients and 200k accounts for each month over a 36 month period.  So for example below if i wanted to see the total client revenue by John Doe I would need the measure to prevode an answer of 22+40= $62.  Before anyone asks I cannot add up the revenue by account instead of by client for various reasons I wont bore you with.  I tried SumX(Values(Client),Calculate(Average(Client revenue)) per recommendations from other posts but its not providng the correct answer.  Any help would be appreciated!

 

Month                    Account  Client           Account Revenue    Client Revenue

January-22123John Doe$10.00$22.00
January-22124John Doe$12.00$22.00
January-22125Jane Doe$17.00$30.00
January-22126Jane Doe$13.00$30.00
February-22123John Doe$15.00$40.00
February-22124John Doe$25.00$40.00
February-22125Jane Doe$11.00$23.00
February-22126Jane Doe$12.00$23.00
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You need to replace the VALUES with SUMMARIZE, because you need to take the month into account too

SUMX(
	SUMMARIZE( 'Table', 'Table'[Client], 'Table'[Month] ),
	CALCULATE( AVERAGE( 'Table'[Client Revenue] ) )
)

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

You need to replace the VALUES with SUMMARIZE, because you need to take the month into account too

SUMX(
	SUMMARIZE( 'Table', 'Table'[Client], 'Table'[Month] ),
	CALCULATE( AVERAGE( 'Table'[Client Revenue] ) )
)

That worked.. Thank you!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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