topic Re: Calculating average of summed values in DAX Commands and Tips

Calculating average of summed values

ThomasSan — Mon, 02 Jan 2023 19:49:31 GMT

Hi everyone,

I have a table regarding the sales of our sales reps. that has the following structure:

Sales Rep	Year	Month	Sales Value
A	2020	5	5 €
B	2020	6	10 €
B	2021	6	11 €
…	…	…	…

I would like to create an average growth of each sales rep so I can say something like "Sales rep E's sales grew by 8% while on average the sales of sales reps A-D grew by 4%".

My initial idea was to use the SUMMARIZE command to create a table like this:

Sales Rep	Sales Value CY	Sales Value PY	Growth
A	465 €	320 €	45%
B	650 €	544 €	19%
…	…	…	…

...from which I was hoping to use the AVERAGE command to calculate the desired value. However, I am uncertain how to actually build a table like this with SUMMARIZE.

Can anyone please help? Or do I even overcomplicate the issue and a simpler DAX approach is possible here?

--------

Here is my current DAX attempt (where I am having difficulties including the growth column):

TEST = ADDCOLUMNS( SUMMARIZE( Table, Table[Sales Rep] ), "CY", CALCULATE( sum(Table[Sales Value]), FILTER( 'Date', year('Date'[Date]) = 2022 ) ), "PY", CALCULATE( sum(Table[Sales Value]), FILTER( 'Date', year('Date'[Date]) = 2021 ) ) )

Re: Calculating average of summed values

Greg_Deckler — Mon, 02 Jan 2023 19:52:45 GMT

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

Re: Calculating average of summed values

ThomasSan — Tue, 03 Jan 2023 07:06:57 GMT

Hi Greg_Deckler ,

thank you for your reply. Your input was what I was looking for!