Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
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
)
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.
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
)
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)
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.