Forum Discussion

NJ81858's avatar
NJ81858
Helper IV
3 years ago

Custom Total Column

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 1Category 2Category 3
Group 10512
Group 25915
Group 312217
Group 47020

 

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 1Category 2Category 3% of Values that are in Category 3
Group 1
051270.588%
Group 2591551.724%
Group 31221754.839%
Group 47020 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!
Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.