Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Original
Jan Feb Mar
a 2 5 6
b 3 6 7
c 1 1
Tot 6 12 13
I want the matrix to be like this(Also months are not fixed)
Jan Feb Mar
a 33% 42% 46%
b 50% 50% 53%
c 17% 8%
Tot 100% 100% 100%
Please help
Great question.
You want to calculate 1) the sum of a number as well as 2) the subtotal of that number ignoring the categories (a, b, c, etc). We can break it down into three measures.
SUM Numbers = SUM(Table[Numbers]) // The ALL in this calculation basically says ignore any filtering by the category field SUM Numbers - Subtotal for Categories = CALCULATE([SUM Numbers], ALL(Table[Categories]) SUM Numbers - % of Category Subtotal = DIVIDE([SUM Numbers],[SUM Numbers - Subtotal for Categories])
Let me know if this makes sense. The thing to watch out for is the ALL function is only referencing the specific category that we're using in this one report - check and see what happens if you replace this category field with another field in the report on the rows.
Hi,
This is exactly what i need to do. However, when I try to reference Sum Numbers in the second measure
SUM Numbers - Subtotal for Categories = CALCULATE([SUM Numbers], ALL(Table[Categories]) it is not available to me. I have defined SUM Numbers in the first step.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |