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.

Frequent Visitor

## Calculate values in one measure using DAX

Hi all,

Simple issue. I have a percentage calculation measure that I'm using in a Card Visual. The issue is, in order to calculate this percentage, I need to calculate the total in one measure, then the filtered total in another, then run the percentage calculation in a third. So I have:

Measure 1) Total
PC_Total = COUNT(Table1[PersonId])

Measure 2) Total of specific value obtained by filter
PC_Provincial = CALCULATE(
COUNTROWS(Table1),
FILTER(ALLSELECTED(Table1), Table1[Jurisd] = "Provincial")
)

Measure 3) Percentage of this filtered total to the absolute total
PC_Provincial_Calc = divide([PC_Provincial],[PC_Total],1)

This works fine, but obviously, if I need to display percentages for additional value types, I need to keep creating two more measures each time. This will obviously get unwieldy very quickly. I'm fine with having one separate total calculation measure, but I'd like the percentage total calculation for a specific filtered value to be one single measure that first counts the filtered total (ala measure 2), then run measure 3 by dividing measure 2 by the absoulate totals measure (measure 1). How would I go about doing this in DAX?

Any guidance appreciated. Thanks.

1 ACCEPTED SOLUTION
New Member

PC_Percentage =
DIVIDE (
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( ALLSELECTED ( Table1 ), Table1[Jurisd] = "Provincial" )
),
COUNT ( Table1[PersonId] ),
0 -- Add a default value for divide to handle potential division by zero
)

3 REPLIES 3
Frequent Visitor

Thank you both for your solutions!

Tried both, @shop-tinh-yeu 's one works fine, but @Ced-Alcaraz 's solution was not accepted by DAX for some reason tho I don't see any issues with it. I get the following when I copy paste as is:

Tried changing it a bit, but didn't work still. Guessing the use of variables is screwing around with it, not sure if my version of PBI has anything to do with it - I'm using Desktop v. 2.114.644.

New Member

PC_Percentage =
DIVIDE (
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( ALLSELECTED ( Table1 ), Table1[Jurisd] = "Provincial" )
),
COUNT ( Table1[PersonId] ),
0 -- Add a default value for divide to handle potential division by zero
)

Regular Visitor

Hi @mmh9119 ,

You can use variables in Power BI Measures.

AllInOneMeasure =

var _PC_Total = COUNT(Table1[PersonId])

var _PC_Provincial = CALCULATE(
COUNTROWS(Table1),
FILTER(ALLSELECTED(Table1), Table1[Jurisd] = "Provincial")
)

RETURN

DIVIDE([_PC_Provincial],[_PC_Total],1)

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