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

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[ID]))

And creating if condition on the above created measure, and also want to slice by date.

Bucket = Switch ( True(),
                           And( [Avg Discount %]>=-10 ,[Avg Discount %] < Sample[Fixed TMax Var 5%]) , "Green",
                           And( [Avg Discount %]>=Sample[Fixed TMax Var 5%],[Avg Discount %] < Sample[Fixed TMax Var 15%]) , "Yellow",
                           And(  [Avg Discount %] >= Sample[Fixed TMax Var 15%],[Avg Discount %] < 500),  "Red"

But The result I am getting from the calculate column Bucket is incorrect.

 

 

The dataset has 49 ID's, But in the Bucket range, it is showing 46+14 = 60 which is incorrect.
Please help me to achieve the expected result. 

PBIX File:  https://www.dropbox.com/s/8o8jo1676fsb36p/SampleB.pbix?dl=0