cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## To calculate percentage based on specific columns in a matrix

Dear All,

Based on the below data I need another column named Percentage that calculates percentage as (Sum of 4 & 5 / Total ) * 100.

The below table is a matrix table. Kindly help.

1 ACCEPTED SOLUTION
Helper III

I found a resolution to this as below:

Measure =
VAR Count45_ = CALCULATE (
COUNT ( VW_DetailLookUp[Response] ),
FILTER (
ALLEXCEPT ( VW_DetailLookUp, VW_DetailLookUp[FileLocation], VW_DetailLookUp[Questions] ),
OR ( [Response]="4-Agree", [Response]="5-StronglyAgree" )
))
VAR CountAll_ = CALCULATE (
COUNT ( VW_DetailLookUp[Response] ),
ALLEXCEPT ( VW_DetailLookUp, VW_DetailLookUp[FileLocation], VW_DetailLookUp[Questions] ))
RETURN
IF (SELECTEDVALUE ( ResponseMaster[Response] ) = "Sum of 4 & 5",
Count45_,
IF (SELECTEDVALUE ( ResponseMaster[Response] ) = "Percent",
DIVIDE(
Count45_,
CountAll_, 0
) * 100,
COUNT ( VW_DetailLookUp[Response] )
))

5 REPLIES 5
Helper III

I found a resolution to this as below:

Measure =
VAR Count45_ = CALCULATE (
COUNT ( VW_DetailLookUp[Response] ),
FILTER (
ALLEXCEPT ( VW_DetailLookUp, VW_DetailLookUp[FileLocation], VW_DetailLookUp[Questions] ),
OR ( [Response]="4-Agree", [Response]="5-StronglyAgree" )
))
VAR CountAll_ = CALCULATE (
COUNT ( VW_DetailLookUp[Response] ),
ALLEXCEPT ( VW_DetailLookUp, VW_DetailLookUp[FileLocation], VW_DetailLookUp[Questions] ))
RETURN
IF (SELECTEDVALUE ( ResponseMaster[Response] ) = "Sum of 4 & 5",
Count45_,
IF (SELECTEDVALUE ( ResponseMaster[Response] ) = "Percent",
DIVIDE(
Count45_,
CountAll_, 0
) * 100,
COUNT ( VW_DetailLookUp[Response] )
))

Helper III

@Arul I did create another measure. But it does not work as expected.

As in the below figure, I need the percentage calculated based on [Sum of 4 or 5] / Total.

Also, the below figure gives an idea of the measure I already have.

Kindly help.

Super User

Thanks,

Arul

Proud to be a Super User!

Helper III

@Arul Thanks for the response.

Super User

try this,

``````Percentage =
VAR _allValues = CALCULATE(
SUM(Table[columnvalue]),ALL(table))
VAR _result = DIVIDE([Sum of 4 & 5],_allValues)*100
RETURN _result``````

Thanks,

Arul

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors