Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to Solution.
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
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
@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.
User | Count |
---|---|
121 | |
69 | |
66 | |
56 | |
52 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |