Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous ,

     

    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

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
6 years ago

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.