cancel
Showing results for
Did you mean:
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%

)

1 ACCEPTED SOLUTION

Accepted Solutions
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

## 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:

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.
Member

## Re: Sum based on Distinct values on another Column

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

After

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

## Re: Sum based on Distinct values on another Column

hi, @sgsukumaran

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