Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
DavidGlover
New Member

Add % Of Total Measures To Matrix

Hi all, I have a matrix like this in Power BI - Table Called 'Table1', Rows from 'Site', Columns from 'Response Type' and Values from 'Count of Response Type'

 

SiteYesNoNot ApplicableTotal 
Washington1236
New York481022

 

I would like to add % columns to each, so instead it would look like this:

SiteYesYes %NoNo %Not ApplicableNot Applicable %TotalTotal %
Washington116.7%233.3%350.0%6100.0%
New York418.2%836.4%10 2245.5%

 

I therefore require a measure/measures that filter for each response and divide it as a percentage of all responses? Can anyone help please?

 

 

2 ACCEPTED SOLUTIONS
v-xuxinyi-msft
Community Support
Community Support

Hi @DavidGlover 

 

Below is the data table I reduced, feel free to correct me if it differs from your data structure.

vxuxinyimsft_0-1736995414043.png

 

You can try to create two measures.

Divide = 
VAR _countAll = CALCULATE(COUNTROWS('Table1'), ALLEXCEPT(Table1, Table1[Site]))
VAR _count = CALCULATE(COUNTROWS('Table1'), 'Table1'[Site] = MAX([Site]), 'Table1'[Response Type] = MAX([Response Type]))
VAR _result = DIVIDE(_count, _countAll)
RETURN
_result

 

% = 
IF(
    HASONEVALUE('Table1'[Response Type]), 
    [Divide], 
    SUMX(ALLEXCEPT('Table1', Table1[Site]), [Divide])
)

 

Output:

vxuxinyimsft_1-1736995793570.png

 

Best Regards,
Yulia Xu

 

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

View solution in original post

Irwan
Super User
Super User

hello @DavidGlover 

 

please check if this accomodate your need.

 

there are at least two ways.

1. Multiple measure as you mentioned above.

- create four measures for "Yes", "No", "Not Applicable", and "Total" with following DAX

Yes % = 
DIVIDE(
    SUM('Table'[Yes]),
    SUM('Table'[Total ])
)
No % = 
DIVIDE(
    SUM('Table'[No]),
    SUM('Table'[Total ])
)
Not Applicable % = 
DIVIDE(
    SUM('Table'[Not Applicable]),
    SUM('Table'[Total ])
)
Total % = 
DIVIDE(
    SUM('Table'[Total ]),
    SUM('Table'[Total ])
)
- plot those measures in matrix visual
Irwan_2-1736996324628.png

 

2. Using Column in Matrix Value

- Unpivot your table therefor you will have category to fill in column at matrix visual

Irwan_1-1736996291503.png

- create a new measure for calculating percent

Percent % = 
var _Attribut = SELECTEDVALUE('Table 2'[Attribute])
var _Site = SELECTEDVALUE('Table 2'[Site])
var _Max =
CALCULATE(
    MAX('Table 2'[Value]),
    FILTER(
        ALL('Table 2'),
        'Table 2'[Site]=_Site&&
        'Table 2'[Attribute]="Total"
    )
)
var _Value =
CALCULATE(
    MAX('Table 2'[Value]),
    FILTER(
        'Table 2',
        'Table 2'[Attribute]=_Attribut&&
        'Table 2'[Site]=_Site
    ),
    'Table 2'[Value]
)
Return
DIVIDE(
    _Value,
    _Max
)
- plot into matrix visual
Irwan_3-1736996413370.png

 

Hope this will help.
Thank you.

View solution in original post

2 REPLIES 2
Irwan
Super User
Super User

hello @DavidGlover 

 

please check if this accomodate your need.

 

there are at least two ways.

1. Multiple measure as you mentioned above.

- create four measures for "Yes", "No", "Not Applicable", and "Total" with following DAX

Yes % = 
DIVIDE(
    SUM('Table'[Yes]),
    SUM('Table'[Total ])
)
No % = 
DIVIDE(
    SUM('Table'[No]),
    SUM('Table'[Total ])
)
Not Applicable % = 
DIVIDE(
    SUM('Table'[Not Applicable]),
    SUM('Table'[Total ])
)
Total % = 
DIVIDE(
    SUM('Table'[Total ]),
    SUM('Table'[Total ])
)
- plot those measures in matrix visual
Irwan_2-1736996324628.png

 

2. Using Column in Matrix Value

- Unpivot your table therefor you will have category to fill in column at matrix visual

Irwan_1-1736996291503.png

- create a new measure for calculating percent

Percent % = 
var _Attribut = SELECTEDVALUE('Table 2'[Attribute])
var _Site = SELECTEDVALUE('Table 2'[Site])
var _Max =
CALCULATE(
    MAX('Table 2'[Value]),
    FILTER(
        ALL('Table 2'),
        'Table 2'[Site]=_Site&&
        'Table 2'[Attribute]="Total"
    )
)
var _Value =
CALCULATE(
    MAX('Table 2'[Value]),
    FILTER(
        'Table 2',
        'Table 2'[Attribute]=_Attribut&&
        'Table 2'[Site]=_Site
    ),
    'Table 2'[Value]
)
Return
DIVIDE(
    _Value,
    _Max
)
- plot into matrix visual
Irwan_3-1736996413370.png

 

Hope this will help.
Thank you.

v-xuxinyi-msft
Community Support
Community Support

Hi @DavidGlover 

 

Below is the data table I reduced, feel free to correct me if it differs from your data structure.

vxuxinyimsft_0-1736995414043.png

 

You can try to create two measures.

Divide = 
VAR _countAll = CALCULATE(COUNTROWS('Table1'), ALLEXCEPT(Table1, Table1[Site]))
VAR _count = CALCULATE(COUNTROWS('Table1'), 'Table1'[Site] = MAX([Site]), 'Table1'[Response Type] = MAX([Response Type]))
VAR _result = DIVIDE(_count, _countAll)
RETURN
_result

 

% = 
IF(
    HASONEVALUE('Table1'[Response Type]), 
    [Divide], 
    SUMX(ALLEXCEPT('Table1', Table1[Site]), [Divide])
)

 

Output:

vxuxinyimsft_1-1736995793570.png

 

Best Regards,
Yulia Xu

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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