cancel
Showing results 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

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-22 123 John Doe \$10.00 \$22.00 January-22 124 John Doe \$12.00 \$22.00 January-22 125 Jane Doe \$17.00 \$30.00 January-22 126 Jane Doe \$13.00 \$30.00 February-22 123 John Doe \$15.00 \$40.00 February-22 124 John Doe \$25.00 \$40.00 February-22 125 Jane Doe \$11.00 \$23.00 February-22 126 Jane Doe \$12.00 \$23.00
1 ACCEPTED SOLUTION
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] ) )
)``````
2 REPLIES 2
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] ) )
)``````
Frequent Visitor

That worked.. Thank you!