Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 😞
Solved! Go to Solution.
Hi @Anonymous
Try this one
RateSupplierExclusivelyHappy = CALCULATE ( DISTINCTCOUNT ( Table1[Supplier] ), FILTER ( ALL ( Table1[Supplier] ), [RateHappy] = 1 ) ) / DISTINCTCOUNT ( Table1[Supplier] )
Hi @Anonymous
Try this one
RateSupplierExclusivelyHappy = CALCULATE ( DISTINCTCOUNT ( Table1[Supplier] ), FILTER ( ALL ( Table1[Supplier] ), [RateHappy] = 1 ) ) / DISTINCTCOUNT ( Table1[Supplier] )
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
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |