Forum Discussion

sgsukumaran's avatar
sgsukumaran
Resolver II
7 years ago
Solved

Sum based on Distinct values on another Column

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...
Need Help
  • sgsukumaran's avatar
    sgsukumaran
    7 years ago

    The calculations did not work and I ended up creating a calculated table with distinct values to achieve.

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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