How to get values in a matrix as a percentage of column total with dynamic cols

[nitisha]

 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

[austinsense]

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.

[android11]

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.