Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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'
Site | Yes | No | Not Applicable | Total |
Washington | 1 | 2 | 3 | 6 |
New York | 4 | 8 | 10 | 22 |
I would like to add % columns to each, so instead it would look like this:
Site | Yes | Yes % | No | No % | Not Applicable | Not Applicable % | Total | Total % |
Washington | 1 | 16.7% | 2 | 33.3% | 3 | 50.0% | 6 | 100.0% |
New York | 4 | 18.2% | 8 | 36.4% | 10 | 22 | 45.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?
Solved! Go to Solution.
Hi @DavidGlover
Below is the data table I reduced, feel free to correct me if it differs from your data structure.
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:
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.
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 ])
)
2. Using Column in Matrix Value
- Unpivot your table therefor you will have category to fill in column at matrix visual
- 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
)
Hope this will help.
Thank you.
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 ])
)
2. Using Column in Matrix Value
- Unpivot your table therefor you will have category to fill in column at matrix visual
- 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
)
Hope this will help.
Thank you.
Hi @DavidGlover
Below is the data table I reduced, feel free to correct me if it differs from your data structure.
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:
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
120 | |
74 | |
72 | |
58 | |
50 |
User | Count |
---|---|
167 | |
83 | |
68 | |
66 | |
55 |