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

     

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous 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.⚡

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.