Sum based on Distinct values on another Column

[sgsukumaran]

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%

)

[v-lili6-msft]

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

[sgsukumaran · Accepted solution]

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

[v-lili6-msft]

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

[v-lili6-msft]

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

[sgsukumaran]

@v-lili6-msft Including all except made the numbers bad.