Averages by time period and cumulative for averages

[Maripo]

Hello, I am trying to calculate averages for selected periods and cumulative totals for those averages. No issues calculating Cumulative total per period for Actuals Quantity.

Actuals Cumulative Total = CALCULATE (DemandAttainment_Sales[Actuals Quantity],

FILTER (ALLSELECTED(Date_Dim),

Date_Dim[Date] <= max(Date_Dim[Date])  ))

But cannot find a way to calculate averages for Current Plan Demand and Cumulative Total for calculated averages (highlighted in the data pic below).  My visual and data should look like that:

Any help is greatly appreciated

t

[Eric_Zhang]

---

@Maripo wrote:

Hello, I am trying to calculate averages for selected periods and cumulative totals for those averages. No issues calculating Cumulative total per period for Actuals Quantity.

Actuals Cumulative Total = CALCULATE (DemandAttainment_Sales[Actuals Quantity],

FILTER (ALLSELECTED(Date_Dim),

Date_Dim[Date] <= max(Date_Dim[Date])  ))

But cannot find a way to calculate averages for Current Plan Demand and Cumulative Total for calculated averages (highlighted in the data pic below).  My visual and data should look like that:

Any help is greatly appreciated

t

---

@Maripo

For the averages for Current Plan Demand，maybe you can try ALLEXCEPT. I can't get idea of Cumulative Total for calculated averages from the pictures, maybe you can share any sample pbix and expected output.

[Maripo]

Hi Eric, I was able to get averages for Demand, but not exactly the way I wanted them to be.

Problem one - my averages are not dynamic.  If I drill up or down in the visual, averages don't change, but I want them to change depending what level I am in - week, month or quarter. Also, I need to calculate Cumulative Total for those Averages, so on my visual I can compare Cumulative for Actual Sales Numbers (column) and Cumulative for Demand Numbers (line).   But regular formula to calculate cumulative doesn’t work on calculated averages. Here is the pbix file https://1drv.ms/u/s!AnlpTlk758V1aUrfkGtqgcArfvc

Thanks!

[Eric_Zhang]

---

@Maripo wrote:

Hi Eric, I was able to get averages for Demand, but not exactly the way I wanted them to be.

Problem one - my averages are not dynamic.  If I drill up or down in the visual, averages don't change, but I want them to change depending what level I am in - week, month or quarter. Also, I need to calculate Cumulative Total for those Averages, so on my visual I can compare Cumulative for Actual Sales Numbers (column) and Cumulative for Demand Numbers (line).   But regular formula to calculate cumulative doesn’t work on calculated averages. Here is the pbix file https://1drv.ms/u/s!AnlpTlk758V1aUrfkGtqgcArfvc

Thanks!

---

@Maripo

Measure won't change according to the drill down level, however you can try some tricks to distiguish the level. Try

Current Plan Demand Weekly Target =
VAR NumberOfMonth =
    DISTINCTCOUNT ( Date_Dim[Year Month] )
VAR NumberOfWeek =
    DISTINCTCOUNT ( Date_Dim[Year Week] )
VAR Months =
    CALCULATE ( DISTINCTCOUNT ( Date_Dim[Year Month] ), ALLSELECTED ( Date_Dim ) )
VAR Quarters =
    CALCULATE ( DISTINCTCOUNT ( Date_Dim[Year Quarter] ), ALLSELECTED ( Date_Dim ) )
VAR Weeks =
    CALCULATE ( DISTINCTCOUNT ( Date_Dim[Year Week] ), ALLSELECTED ( Date_Dim ) )
VAR CurrentPlanDemand =
    CALCULATE (
        Demand_Attainment_RRDemand[Current Plan Demand],
        ALLSELECTED ( Date_Dim )
    )
RETURN
    SWITCH (
        TRUE (),
        [Number Of Month] > 1, //Quarter level
        CurrentPlanDemand / Quarters,
        [Number Of Month] = 1
            && [Number Of Week] > 1, //month level
        CurrentPlanDemand / [Number Of Weeks In Qtr]
            / Months,
        //Otherwise, week level)
        CurrentPlanDemand / Weeks
    )

As to "Cumulative Total for those Averages", I'm still not getting it. I know Cumulative Total or average, but what is Cumulative average? Could you be more specific?

[Maripo]

Genious!  Thanks a lot, Averages work as expected.  Now I need to calculate Cumulative (running) total for Current Plant Demand Weekly Target values (the one you just helped me to calculate).  So it would look like in the image below.  I will be using Cumulative total of Current Plant Demand Weekly Target along with Actuals Cumulative Total in the visual.   Let me know if it still doesn't make sense 

[Eric_Zhang]

---

@Maripo wrote:

Genious!  Thanks a lot, Averages work as expected.  Now I need to calculate Cumulative (running) total for Current Plant Demand Weekly Target values (the one you just helped me to calculate).  So it would look like in the image below.  I will be using Cumulative total of Current Plant Demand Weekly Target along with Actuals Cumulative Total in the visual.   Let me know if it still doesn't make sense 

---

@Maripo

For me, the Cumulative average doesn't make too much sense. 

However, you can still play some tricks. Just index the Quarter(1,2,3), Month(1,2,3,4...), Week(1,2,3,4,5,6,7...), so in different drill down level, use different index individually(as in the measure "Current Plan Demand Weekly Target"). For example, in week level, use the week index, so the MAX(weekIndex) from left to right is 1,2,3,4... and so on. The multiple the "Current Plan Demand Weekly Target" by the weekIndex.

[Maripo]

Cumulative of the average is a requirement from the user. He needs to see where we are in terms of fulfilling the demand over time. Demand number comes as one number versus actuals spread accross time period. That's why we spread the demand equally between time periods (averages) and then compare cumulative actuals to cumulative demand.

Thank you very much for your help, I will try the approach you suggested for cumulatives.

[Eric_Zhang]

---

@Maripo wrote:

Cumulative of the average is a requirement from the user. He needs to see where we are in terms of fulfilling the demand over time. Demand number comes as one number versus actuals spread accross time period. That's why we spread the demand equally between time periods (averages) and then compare cumulative actuals to cumulative demand.

Thank you very much for your help, I will try the approach you suggested for cumulatives.

---

@Maripo

If you have any question, feel free to let me know.:)

[Eric_Zhang · Accepted solution]

@Maripo

How is everything going? If there's no further questions, can you accept the replies making sense to close this thread? For any question, just feel free to let me know. 🙂