Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mmh9119
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
shop-tinh-yeu
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
)

View solution in original post

3 REPLIES 3
mmh9119
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:

mmh9119_0-1701787973296.png

 

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. 

shop-tinh-yeu
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
)

Ced-Alcaraz
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)

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors