cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sgsukumaran Member
Member

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 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%


)Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
sgsukumaran Member
Member

Re: Sum based on Distinct values on another Column

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

5 REPLIES 5
Community Support Team
Community Support Team

Re: Sum based on Distinct values on another Column

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:

13.PNG

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sgsukumaran Member
Member

Re: Sum based on Distinct values on another Column

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

Capture1.PNG

 

 

 

Community Support Team
Community Support Team

Re: Sum based on Distinct values on another Column

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

18.PNG

After 

17.PNG

If not your case, please share your complete screenshot of this page, Do mask sensitive data before uploading. 

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Sum based on Distinct values on another Column

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sgsukumaran Member
Member

Re: Sum based on Distinct values on another Column

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