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.
I have the below table displayed in a report:
State | Discounted Sales | All Sales |
MA | 50 | 200 |
SC | 100 | 500 |
NY | 150 | 300 |
I want the below [Discounted % of Total] column to be created below:
State | Discounted Sales | All Sales | Discounted % of Total |
MA | 50 | 200 | 5% |
SC | 100 | 500 | 10% |
NY | 150 | 300 | 15% |
Total | 300 | 1000 | 30% |
The issue is everytime I create a new measure and try to calculate the All Sales Total (1000) it automatically groups it by ['State'], so using first row as an example, it displays 25% (50/200) intead of 5% (50/1000). I've tried CALCULATE, SWITCH, FILTER etc and all with the same result.
Also in my report I have a date slicer by year, quarter and month which pulls from another [Date] Column, so when I apply the slicer by Date, I want table to update. So for example, in the first row, if I filtered by 2021, and the total number of sales was 500 instead of 1000, [Discounted % of Total] would update to 10% (50/500)
I've been able to find the total sales figure by using the MAX aggregation on [All Sales] bu then the number doensn't respond to the slicer.
Thanks in advance!
Solved! Go to Solution.
@Anonymous
pls try this
% of total = sum('Table'[Discounted Sales])/CALCULATE(sum('Table'[All Sales]),all('Table'))
pls see the attachment below
Proud to be a Super User!
Discounted % of total =
VAR currentValue =
CALCULATE ( SUM ( 'Table'[Discounted Sales] ) )
VAR totalValue =
CALCULATE ( SUM ( 'Table'[Discounted Sales] ), ALLSELECTED ( 'Table' ) )
RETURN
DIVIDE ( currentValue, totalValue )
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 |
---|---|
96 | |
92 | |
81 | |
70 | |
64 |
User | Count |
---|---|
115 | |
106 | |
96 | |
81 | |
72 |