Forum Discussion

sgsukumaran's avatar
sgsukumaran
Resolver II
7 years ago
Solved

Average Calculation

 

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)

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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
)

    Average_Calculation

     

    Best Regards,

    Dale

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      sgsukumaran
      Resolver II
      7 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        My apologies.

         

        I hope you will find this link useful. 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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
)

    Average_Calculation

     

    Best Regards,

    Dale