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       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

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

     

     

4 Replies

  • 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.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    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