Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Antara
Helper I
Helper I

Static Average By Creating Bicket start from 01/07/2020

Hello Experts,

 

I have below input data:

 

DateKey            LocationID       MeasureValue

01-07-2020       100                   4000

02-07-2020       100                   6000

03-07-2020       100                   2000

01-07-2020       101                   9000

02-07-2020       101                   6000

03-07-2020       101                   3000

04-07-2020       100                   1000

05-07-2020       100                   1000

06-07-2020       100                   4000

04-07-2020       101                   3000

05-07-2020       101                   3000

 

 

I want to do bucketing(3 days) based on DateKey and LocationID  and show below output:

 

DateKey            LocationID       MeasureValue    Bucket number          AvgOfMeasureValue

01-07-2020       100                   4000                    1                               4000

02-07-2020       100                   6000                    1                               4000

03-07-2020       100                   2000                    1                               4000

01-07-2020       101                   9000                    1                               6000

02-07-2020       101                   6000                    1                               6000

03-07-2020       101                   3000                    1                             6000

04-07-2020       100                   1000                     2                              2000

05-07-2020       100                   1000                     2                            2000

06-07-2020       100                   4000                     2                              2000

04-07-2020       101                   3000                    2                              4500

05-07-2020       101                   6000                    2                              4500

 

Can we achieve above using DAX??

Any suggestion or help would be appreciated

Thanks

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @Antara ,

 

Just a little modification on HotChilli's reply(Measure value seems not to be column in table):

 

AVERAGEVALUE =
AVERAGEX (
    FILTER (
        ALL ( Table ),
        Table[Bucket] = MIN ( Table[Bucket] )
            && Table[LocationID] = MIN ( Table[LocationId] )
    ),
    [Measure]
)

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

 

View solution in original post

4 REPLIES 4
v-deddai1-msft
Community Support
Community Support

Hi @Antara ,

 

Just a little modification on HotChilli's reply(Measure value seems not to be column in table):

 

AVERAGEVALUE =
AVERAGEX (
    FILTER (
        ALL ( Table ),
        Table[Bucket] = MIN ( Table[Bucket] )
            && Table[LocationID] = MIN ( Table[LocationId] )
    ),
    [Measure]
)

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

 

parry2k
Super User
Super User

@Antara add following measure

 

Avg = 
CALCULATE ( AVERAGE ( Table[MeasureValue] ), ALLEXCEPT ( Table, Table[LocationId] ) ) 

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank You @parry2k  for the response. I tried the approach but it does not match the excpected output in problem statement. Could you please suggest.

 

 

Thanks

A column for the bucket:

Bucket = ROUNDUP(DIVIDE(DAY(TableM[DateKey]), 3), 0)

 

A measure for the average:

AvgForBucketLocation = CALCULATE(AVERAGE(TableM[MeasureValue]), FILTER(ALL(TableM), TableM[Bucket] = MIN(TableM[Bucket]) && TableM[LocationID] = MIN(TableM[LocationId])))

 

There's a mismatch between the data tables in the provided data ( last 2 rows are 3000,3000 in before data and 3000,6000 in the after).

Good luck.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors