Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
)
)
)
Solved! Go to 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 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.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |