Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate percentage based on another measure percentage

Hi everyone,

 

I need help to calculate percentage based on another measure percentage. 

 

I have suppliers and these suppliers are happy or not in each line.

 

So, first I count the number of rows ( TotalCount = DISTINCTCOUNT(Table1[Id]) ) = 9

 

Then, I count the number of rows where the supplier is happy ( TotalCountHappy = CALCULATE(DISTINCTCOUNT(Table1[Id]), Table1[IsHappy]=1) ) = 6

 

Then, the rate where suppliers are happy ( RateHappy = [TotalCountHappy]/[TotalCount] ) = 66.67%

 

Then, the rate of suppliers are unless one time happy ( RateSupplierHappy = CALCULATE(DISTINCTCOUNT(Table1[Supplier]),Table1[IsHappy]=1)/DISTINCTCOUNT(Table1[Supplier]) ) = 75%

 

And now, I want the rate of suppliers where they are exclusively happy, so simply it's the rate of suppliers where RateHappy = 100%, so I tried ( RateSupplierExclusivelyHappy = CALCULATE(DISTINCTCOUNT(Table1[Supplier]), [RateHappy]=1)/DISTINCTCOUNT(Table1[Supplier]) ) but successless.. according to the picture below :

 

So, if anyone could help me please, I'm really stuck 😞

 

Capture d’écran 2018-03-22 à 20.54.04.png

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

Hi @Anonymous

 

Try this one

 

RateSupplierExclusivelyHappy =
CALCULATE (
    DISTINCTCOUNT ( Table1[Supplier] ),
    FILTER ( ALL ( Table1[Supplier] ), [RateHappy] = 1 )
)
    / DISTINCTCOUNT ( Table1[Supplier] )

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

Hi @Anonymous

 

Try this one

 

RateSupplierExclusivelyHappy =
CALCULATE (
    DISTINCTCOUNT ( Table1[Supplier] ),
    FILTER ( ALL ( Table1[Supplier] ), [RateHappy] = 1 )
)
    / DISTINCTCOUNT ( Table1[Supplier] )

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Thank you @Zubair_Muhammad, It works like a charm ! 

Anonymous
Not applicable

Hi Valentin,

 

You should try something like this:

 

RateSupplierExclusivelyHappy = CALCULATE(DISTINCTCOUNT(Table1[Supplier]),

FILTER(Table1,[RateHappy]=1))/

DISTINCTCOUNT(Table1[Supplier])

 

This should work, the error you are getting is because of the sintaxis you are using with calculate.

You can not use measures as filter directly in calculate, and if you want to use it you need to use the FILTER formula.

 

Please give it a try.

 

Best,

Andres

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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