Average YTD Calculation Based on User Selection

[PowerBI-Newbie]

Hi,

I have the following data:

I am able to filter out by each year and period and the correct YTD figure displays thanks to help from Power BI Superusers:

I now want to change the YTD from being running total to average depending on the user selection.

So the following is the intention when the user selects the relevant Period for a particular year:

Year	Period	Average Planned YTD
19-20	1	9
19-20	2	=(9+16)/2 =12.5
19-20	3	=(9+16+15)/3=13.3
19-20	4	=(9+16+15+14)/4=13.5

Please note that the Year and Period slicers are already related to the Period and YTD tables, that's how I've managed to get the right figures for Period and YTD.

Any help is greatly appreciated.

[Icey]

Hi @PowerBI-Newbie ,

Try this:

Average Planned YTD = CALCULATE ( AVERAGE ( 'Table'[Planned] ), ALLSELECTED ( 'Table'[Period] ) )

 

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerBI-Newbie]

Thanks for your response @Icey but I need the user to select only one period at a time from the slicer and when they do then the correct value is displayed. If they select more than one then the correct averages are displayed for each period. Your images show the incorrect YTD averages for other periods when more than one selection is made - in your example, when you select more than one period the average is displaying the same.

If you look at my table in my post, the user selects only Period 1, average YTD 9 is displayed. User selects only Period 2 then 12.5 is displayed. If user selects Period 1 and 2 then 9 is displayed for Period 1 and 12.5 for Period 2.

I hope that makes sense.

[Icey · Accepted solution]

Hi @PowerBI-Newbie ,

How about this:

Average Planned YTD =
VAR SelectedPeriod =
    SELECTEDVALUE ( 'Table'[Period] )
VAR MaxPeriod =
    MAX ( 'Table'[Period] )
RETURN
    IF (
        SelectedPeriod = BLANK (),
        [Planned (YTD)] / MaxPeriod,
        [Planned (YTD)] / SelectedPeriod
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerBI-Newbie]

Hi @Icey ,

Thank you for your response. Your solution worked for a lot of my KPIs apart from one. The data is as follows:

Period	Area	Allocation
1	A	195.03
1	B	266.81
2	A	201.86
2	B	269.18
3	A	200.23
3	B	259.54

I'm calculating the sum of Areas A & B but subtract 26.5 so total for:

• Period 1 is 435.34
• Period 2 is 444.54
• Period 3 is 433.27

The averages should then read:

1. Period 1=435.34
2. Period 2=439.94
3. Period 3=437.72

My Periodic Measure is:

Allocation (FTE) = (sum(Budget_Actuals_Demand_Allocation[Allocation (FTE)-Pre]) - 26.5)

For Period 1 I'm getting:

But Period 2 produces the following:

Many thanks for your help thus far.

[Icey]

Hi @PowerBI-Newbie 

What do the numbers in the screenshots you provide represent? And how is it calculated?

Best Regards,

Icey

[PowerBI-Newbie]

Hi @Icey ,

Apologies for not being clear. The set of numbers on the left side represent the periodic figures for certain KPIs while the right side represent the YTD. What we're focused on is the first figure on the left and right (435) which represents the Allocation KPI.

The left-side figures (periodic) for Allocation are calculated using the following measure:

Allocation (FTE) = (sum(Budget_Actuals_Demand_Allocation[Allocation (FTE)-Pre]) - 26.5)

It's calculating the sum of Areas A & B minus 26.5:

• Period 1 is 435.34
• Period 2 is 444.54
• Period 3 is 433.27

The data is from the following table:

Period	Area	Allocation
1	A	195.03
1	B	266.81
2	A	201.86
2	B	269.18
3	A	200.23
3	B	259.54

The right-side figures (average YTD) for Allocation are calculated using the following measure (which you gave me):

Allocation (FTE) (YTD) =

VAR SelectedPeriod =

SELECTEDVALUE ( 'Calendar_Table'[Period] )

VAR MaxPeriod =

MAX ( 'Calendar_Table'[Period] )

RETURN

IF (

SelectedPeriod = BLANK (),

[Allocation (FTE)] / MaxPeriod,

[Allocation (FTE)] / SelectedPeriod

)

So the averages should then read:

1. Period 1=435.34
2. Period 2=439.94
3. Period 3=437.72

But I'm getting:

1. Period 1=435 (correct value)
2. Period 2=222 (incorrect value)
3. Period 3=144 (incorrect value)

I hope that's clear.

[PowerBI-Newbie]

Hi @Icey ,

Did you an opportunity to have a look at my query from my last post?