Forum Discussion

anil's avatar
anil
Helper III
8 years ago
Solved

Creating Range Buckets using Average per Category ID

Hi,       My dataset has ID's with multiple orders. I am trying to calculate average discount across orders per ID. Avg Discount % = CALCULATE(AVERAGE(Sample[DISCOUNTPERCENT]),Sample,VALUES(Sample[...
Need Help
Show and Tell
Tips and Tricks
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi anil,

     

    That's because the "Allexcept" cleared the context. Please try the formula below or adjust it.

    Avg Discount % =
CALCULATE (
    AVERAGE ( Sample[DISCOUNTPERCENT] ),
    ALLEXCEPT ( 'Sample', Sample[ID], 'Sample'[Order Date].[Year] )
)

    Best Regards,

    Dale

anil's avatar
anil
Helper III
8 years ago

Please help me with solution. Tried different methods but unable to crack it.

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi anil,

 

The [Avg Discount %C] works in my test. Please try it as a measure.

Avg Discount % =
CALCULATE (
    AVERAGE ( Sample[DISCOUNTPERCENT] ),
    ALLEXCEPT ( 'Sample', Sample[ID] )
)

Creating_Range_Buckets_using_Average_per_Category_ID

 

Best Regards,

Dale

  • anil's avatar
    anil
    Helper III
    8 years ago

    Thanks Dale for the reply,

                      If we use the measure the average is constant even if we slice using the date.  Which is incorrect.

    Eg. If we filter ID say: E50XN and filter year = 2010 the  Average remains the same 45.43, where the actual Average is  100.

    What I understood is the created measure is not affected by the date slicer.

     

     

     

    • anil's avatar
      anil
      Helper III
      8 years ago

      Please help me with solution. Tried different methods but unable to crack it.

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

        Hi anil,

         

        That's because the "Allexcept" cleared the context. Please try the formula below or adjust it.

        Avg Discount % =
CALCULATE (
    AVERAGE ( Sample[DISCOUNTPERCENT] ),
    ALLEXCEPT ( 'Sample', Sample[ID], 'Sample'[Order Date].[Year] )
)

        Best Regards,

        Dale