cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anil Member
Member

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

And creating a column to bucket the averages of ID's.

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

 

 

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. 

 

Also tried it in a different method. First created a calculated column for Average Discount per ID:

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

But this calculated column I am unable to filter by dates. When I filter by say Year Month the average values are constant per ID. The filters are not applied to the calculated column.

 

Help me with the calculation to filter the column by date so I can implement the column to create the bucket.

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Creating Range Buckets using Average per Category ID

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
anil Member
Member

Re: Creating Range Buckets using Average per Category ID

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

anil Member
Member

If condition on 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[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.B1.JPG

 

 

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

 

 

Community Support Team
Community Support Team

Re: Creating Range Buckets using Average per Category ID

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
anil Member
Member

Re: Creating Range Buckets using Average per Category ID

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

 

 

 

anil Member
Member

Re: Creating Range Buckets using Average per Category ID

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

Community Support Team
Community Support Team

Re: Creating Range Buckets using Average per Category ID

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
anil Member
Member

Re: Creating Range Buckets using Average per Category ID

Thank You @v-jiascu-msft