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.
I am trying to ratio based on sum(unique values on a column)/ Count(Values on Column)
Warehouse Processing %:=
DIVIDE (
CALCULATE (
SUM ( [Warehouse Processing Offset] ),
'Orders'[DC Ready To Ship Date] <> BLANK ()
),
CALCULATE (
DISTINCTCOUNT(Orders[Pick Ticket Control Number] ),
'Orders'[DC Ready To Ship Date] <> BLANK ()
),
0)
Essentially for a distinct Order and pickticket number the % would be (0+1)/2 = 50%
)
Solved! Go to Solution.
The calculations did not work and I ended up creating a calculated table with distinct values to achieve.
hi, @sgsukumaran
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Best Regards,
Lin
The calculations did not work and I ended up creating a calculated table with distinct values to achieve.
hi,@sgsukumaran
After my test, I didn't notice that there is one slicer "Event year/month" in your report, So please be careful with these
details when we use ALLEXCEPT Function, try this formula:
Warehouse Processing % = DIVIDE ( CALCULATE ( SUM ( [Warehouse Processing Offset] ), 'Orders'[DC Ready To Ship Date] <> BLANK () ), CALCULATE ( DISTINCTCOUNT ( Orders[Pick Ticket Control Number] ),ALLEXCEPT(Orders,Orders[Order Number],Orders[Year Month]), 'Orders'[DC Ready To Ship Date] <> BLANK () ), 0 )
Result:
Before
After
If not your case, please share your complete screenshot of this page, Do mask sensitive data before uploading.
Best Regards,
Lin
hi,@sgsukumaran
After my research, you need use ALLEXCEPT Function in your function like below:
Warehouse Processing % = DIVIDE ( CALCULATE ( SUM ( [Warehouse Processing Offset] ), 'Orders'[DC Ready To Ship Date] <> BLANK () ), CALCULATE ( DISTINCTCOUNT ( Orders[Pick Ticket Control Number] ),ALLEXCEPT(Orders,Orders[Order Number]), 'Orders'[DC Ready To Ship Date] <> BLANK () ), 0 )
Result:
Best Regards,
Lin
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |