Forum Discussion

PowerBI-Newbie's avatar
PowerBI-Newbie
Helper IV
6 years ago
Solved

Average YTD Calculation Based on User Selection

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

    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.

Icey's avatar
Icey
Community Support
6 years ago

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's avatar
PowerBI-Newbie
Helper IV
6 years ago

Hi Icey ,

 

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

PeriodAreaAllocation
1A195.03
1B266.81
2A201.86
2B269.18
3A200.23
3B259.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's avatar
    Icey
    Community Support
    6 years ago

    Hi PowerBI-Newbie 

     

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

     

     

    Best Regards,

    Icey

    • PowerBI-Newbie's avatar
      PowerBI-Newbie
      Helper IV
      6 years ago

      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:

      PeriodAreaAllocation
      1A195.03
      1B266.81
      2A201.86
      2B269.18
      3A200.23
      3B259.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's avatar
        PowerBI-Newbie
        Helper IV
        6 years ago

        Hi Icey ,

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