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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |