Average Calculation

[sgsukumaran]

I am trying to calulate ratio based on unique values on another column.  Eventually I want to sum the offset only based on unique "pick ticket contol number" and divide it by discount count of  "pick ticket contol number".  Based on the data below for a distinct pickticket number the % would be  (0+1)/2(distinct count) = 50%. Any help is highly appreciated.

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)

[v-jiascu-msft]

Hi @sgsukumaran,

Could you please mark the proper answers as solutions?

Best Regards,
Dale

[v-jiascu-msft · Accepted solution]

Hi @sgsukumaran,

Try this formula, please. If it doesn't work, please share a dummy sample file.

Warehouse Processing % =
DIVIDE (
    CALCULATE (
        SUMX (
            SUMMARIZE (
                Orders,
                Orders[Pick Ticket Control Number],
                Orders[Warehouse Processing Offset]
            ),
            [Warehouse Processing Offset]
        ),
        ALL ( Orders )
    ),
    CALCULATE (
        DISTINCTCOUNT ( Orders[Pick Ticket Control Number] ),
        ALL ( Orders )
    ),
    0
)

Best Regards,

Dale

[Anonymous]

I am assuming that the logic in your DAX is the intended logic.

If so, please break the individual numerator and denominator as variables and then use the variables in the divide function.

https://www.sqlbi.com/articles/variables-in-dax/

Please let me know if this helps.

[sgsukumaran]

@Anonymous This should return the same result. What is different here?

[Anonymous]

My apologies.

I hope you will find this link useful. 

[sgsukumaran]

@Anonymous - It is the same as i have now. Thanks