Calculating average of summed values

[ThomasSan]

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
    )
    )
)

[Greg_Deckler · Accepted solution]

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

[ThomasSan]

Hi @Greg_Deckler ,

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