cancel
Showing results for
Did you mean:

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.

Helper IV

## 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 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!
Community Support

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors