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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.