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.
Is it possible to have the total in a table average the values when sum aggregation is used?
I have a table basically showing share of sales (%) by company. I'm using Sum aggregation and displaying the value as a percent of the grand total. In the underlying data, there are multiple records per company that need to be totalled. I'm also using a Top N filter, if that's relevant. My question is, can I change the total row to take the average of the % share for each line?
For example, the underlying data looks like this:
Company | Sales |
A | 10 |
A | 15 |
A | 20 |
B | 25 |
B | 30 |
C | 35 |
C | 40 |
C | 45 |
C | 50 |
D | 5 |
D | 10 |
E | 15 |
And the table I have would look like this:
Company | Share |
A | 15% |
B | 18% |
C | 57% |
Average | 30% |
The total by default shows 100% since it's showing the percent of the grand total, and I want it to show the average of each line. In this case 30% = average(15%,18%,57%).
Solved! Go to Solution.
Hi,
You may download my solution file from here.
Hope this helps.
@ELW , Try like
averageX(summarize(Table, Table[company], "_1",SUM ( Table[Sales] ),"_2", CALCULATE(SUM(]Sales[Sales]),ALL(Sales))),[_1])
Hi,
You may download my solution file from here.
Hope this helps.
You are welcome.
Please try this expression in a measure to get your desired result in a table visual with the Company column.
Avg =
VAR __overalltotal =
CALCULATE ( SUM ( Sales[Sales] ), ALL ( Sales ) )
RETURN
AVERAGEX (
VALUES ( Sales[Company] ),
CALCULATE ( SUM ( Sales[Sales] ) ) / __overalltotal
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |