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.
Hello,
I have a large matrix set up and would like to set up a measure that calculates the total row percentage. ie:
Item Qtr1 Qtr2 Qtr3 Qtr4 Total
A 100 300 200 400 1000
The measure would calculate as
Item Qtr1 Qtr2 Qtr3 Qtr4 Total
A 10% 30% 20% 40% 100%
I also have a handful of page level filters that I have to account for. I tried
%ofTotal = DIVIDE(SUM(Table[Sales]),CALCULATE(SUM(Table[Sales]), ALLEXCEPT(Table,Table[Sales]
This returns a percentage of total sales for the entire table. Please help.
Solved! Go to Solution.
hi @Sean and @Anonymous
To calculate the % for each item this is the dax sentence:
% of Total Measure = DIVIDE ( CALCULATE ( SUM ( Table1[Sales] ) ), CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1[Quarter], Table1[Item Description] ) ), 0 )
And if you want 100% for each Item Description.
% of Total Measure = DIVIDE ( CALCULATE ( SUM ( Table1[Sales] ) ), CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1[Quarter] ) ), 0 )
@Anonymous I think this should work!
% of Total Measure = DIVIDE ( SUM ( 'Table'[Sales] ), CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Item] ) ), 0 )
Thanks for the responsse. That didn't do what I wanted it to though.
Here is a snippet of the return. The final % of Total should be 100% for every row. the first % of Total should be ($3,632.20/$1,743,851.20)=0.2%, not 0.08% as shown. Would this have to do with my report level filters?
How many fields do you have in the Rows of the Matrix and in what order and which one are you using to filter?
Two fields, Item ID and Item description. Neither of those is filtered. My filters are report level.
Customer type: Not a National or government account
Region: Just US and CAN regions
@Anonymous its noon here and i have to go - but I'm sure @KHorseman can help you with this!
The order of the fields matters (I was going by your original sample)
So how are Customer Type and Region related to ItemID and Item Desciption?
So this is data at the transactional level. Every customer has a Tier and are in a certain region. Item ID and Description are based off that particular sale to that particular customer.
@KHorseman @Vvelarde Can you do % of Row Total so the % adjusts to 100% even after you use Slicers from a related table?
Current Measure
% of Total Measure = DIVIDE ( SUM ( 'Table'[Sales] ), CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Item ID] ) ), 0 )
Here's the Sample Data I created
Table 1
Item ID | Item Description | Quarter | Sales | Customer |
A | Category 1 | Q1 | 50 | Customer 1 |
A | Category 2 | Q1 | 50 | Customer 2 |
A | Category 1 | Q2 | 150 | Customer 2 |
A | Category 2 | Q2 | 150 | Customer 3 |
A | Category 1 | Q3 | 100 | Customer 3 |
A | Category 2 | Q3 | 100 | Customer 1 |
A | Category 1 | Q4 | 200 | Customer 1 |
A | Category 2 | Q4 | 200 | Customer 4 |
B | Category 1 | Q1 | 250 | Customer 1 |
B | Category 2 | Q1 | 250 | Customer 2 |
B | Category 1 | Q2 | 0 | Customer 2 |
B | Category 2 | Q2 | 0 | Customer 3 |
B | Category 1 | Q3 | 250 | Customer 3 |
B | Category 2 | Q3 | 250 | Customer 1 |
B | Category 1 | Q4 | 0 | Customer 1 |
B | Category 2 | Q4 | 0 | Customer 4 |
C | Category 1 | Q1 | 0 | Customer 1 |
C | Category 2 | Q1 | 0 | Customer 2 |
C | Category 1 | Q2 | 125 | Customer 2 |
C | Category 2 | Q2 | 125 | Customer 3 |
C | Category 1 | Q3 | 0 | Customer 3 |
C | Category 2 | Q3 | 0 | Customer 1 |
C | Category 1 | Q4 | 375 | Customer 1 |
C | Category 2 | Q4 | 375 | Customer 4 |
D | Category 1 | Q1 | 0 | Customer 1 |
D | Category 2 | Q1 | 0 | Customer 2 |
D | Category 1 | Q2 | 0 | Customer 2 |
D | Category 2 | Q2 | 0 | Customer 3 |
D | Category 1 | Q3 | 0 | Customer 3 |
D | Category 2 | Q3 | 0 | Customer 1 |
D | Category 1 | Q4 | 500 | Customer 1 |
D | Category 2 | Q4 | 500 | Customer 4 |
Table 2
Customer | Customer Type | Region |
Customer 1 | Govt | USA |
Customer 2 | Non Govt | USA |
Customer 3 | Govt | Canada |
Customer 4 | Non Govt | Canada |
hi @Sean and @Anonymous
To calculate the % for each item this is the dax sentence:
% of Total Measure = DIVIDE ( CALCULATE ( SUM ( Table1[Sales] ) ), CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1[Quarter], Table1[Item Description] ) ), 0 )
And if you want 100% for each Item Description.
% of Total Measure = DIVIDE ( CALCULATE ( SUM ( Table1[Sales] ) ), CALCULATE ( SUM ( Table1[Sales] ), ALL ( Table1[Quarter] ) ), 0 )
Hi
I need some help..
I have a table
Date(Bins) Type A Type B Type C RowTotal (I created this measure adding the row)
Jan 2016 30 276 342 648
Feb 2016 24 269 307 600
Mar 2016 41 332 286 659
I need to be able to add columns Type A % of RowTotal
Type B % of Row Total
Type C % of Row Total
and display that in 3 columns for each of the months or rows like shown above
Anyhelp? Thanks in advance
Hi
I need some help..
I have a table
Date(Bins) Type A Type B Type C RowTotal (I created this measure adding the row)
Jan 2016 30 276 342 648
Feb 2016 24 269 307 600
Mar 2016 41 332 286 659
I need to be able to add columns Type A % of RowTotal
Type B % of Row Total
Type C % of Row Total
and display that in 3 columns for each of the months or rows like shown above
Anyhelp? Thanks in advance
I am struggling the with a similar issue.
I have per day sales over a month and want the weekly % total as well as the % of the month.
If I wanted to slice the months data down to the week of the month will these measures still work?
I was using this as a reference but not sure that results will be what I am looking for:
http://databear.com/2016/05/26/power-bi-tips-calculate-one-of-the-most-used-dax-functions/
So this is data at the transactional level. Every customer has a Tier and are in a certain region. Item ID and Description are based off that particular sale to that particular customer.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |