Custom Total Column

[NJ81858]

Hello,

I am trying to create a custom calculation for my total column in a matrix and I'm not sure how to configure that best. My matrix looks like this currently, with the Groups being my rows and my Categories being my columns:

	Category 1	Category 2	Category 3
Group 1	0	5	12
Group 2	5	9	15
Group 3	12	2	17
Group 4	7	0	20

What I am trying to calculate in my custom total column is the percentage of values that are in Category 3 for each group. In theory I want my matrix to look like this:

	Category 1	Category 2	Category 3	% of Values that are in Category 3
Group 1	0	5	12	70.588%
Group 2	5	9	15	51.724%
Group 3	12	2	17	54.839%
Group 4	7	0	20	74.074%

I tried to create a measure that calculates this and it currently looks like this: 

VAR _numTotal = CALCULATE(

    COUNT([Value]),

        FILTER('Table', [Group] = [Group]))

VAR _numCategory3 = CALCULATE(

    COUNT([Value]),

        FILTER('Table', [Categories] = "Category 3"),

        FILTER('Table', [Group] = [Group]))

RETURN CALCULATE(

    DIVIDE(_numCategory3, _numTotal),

    FILTER('Table', [Group] = [Group]))

Any help on this is greatly appreciated, thank you in advance!

[v-tangjie-msft]

Hi @NJ81858 ,

According to your description, here are my steps you can follow as a solution.

(1) We can create a measure or calculated column.

Measure = 
var _a=CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Group]),'Table'[Value]))
var _b=CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Category]="Category 3"))
var _c=DIVIDE(_b,_a,0)
return _c

Column = var _a = IF('Table'[Category]="Category 3",'Table'[value])

var _b = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Group]=EARLIER('Table'[Group])))

return DIVIDE(_a,_b,0)

(2) Then the result is as follows.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.