Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
90 | |
88 | |
70 | |
69 |
User | Count |
---|---|
227 | |
127 | |
118 | |
82 | |
80 |