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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ThomasSan
Helper III
Helper III

Calculating average of summed values

Hi everyone,

 

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

Sales RepYearMonthSales Value
A202055 €
B2020610 €
B2021611 €

 

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 RepSales Value CYSales Value PYGrowth
A465 €320 €45%
B650 €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
    )
    )
)

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

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


@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors