Forum Discussion

cesarvinas's avatar
cesarvinas
Icon for Advocate I rankAdvocate I
6 years ago
Solved

How to duplicate values from grouped measures into additional virtual groups

Hi. I have a table with places and corresponding population per year. Currently I only have data up to year 2018. Somethin like this:

 

PlaceYearPopulation
a201712
a201811
b201743
b201821

 

I've created a measure that sums the "Population" column values and used it in a columns chart with the "Year" column as the axis to see the total population per year. The data in the chart would be:

 

YearTotal
201755
201833

 

I need the chart to also show years 2019 and 2020 with the same value from 2020, like:

 

YearTotal
201755
201833
201933
202033

 

How can I achieve the above via DAX. I basically need to take the "Total" from the MAX "Year" and dynamically create two additional entries for 2019 and 2020 with the same total from that MAX "Year".

 

My use case is more involved as then I will have to add another measure to the same columns chart to show how many of that total population attended a particular class. My table with the "class attendance" data does have values up to 2020 which is why I need the total population to go all the way to 2020 and assume that the population for 2019 and 2020 is the same than in 2018 so then I can compare that with class attendance numbers.

 

Any help or guidance is welcomed.

 

Thanks.

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Now that you have your Years table, you could use an expression like this for your population to get the most recent non-blank population

     

    Population =
VAR __thisyear =
    SELECTEDVALUE ( Years[Year] )
VAR __summary =
    ADDCOLUMNS (
        FILTER ( ALL ( Years[Year] ), Years[Year] <= __thisyear ),
        "@Pop", CALCULATE ( SUM ( PlacePopulation[Population] ) )
    )
VAR __Top1 =
    TOPN ( 1, FILTER ( __summary, [@Pop] > 0 ), Years[Year] )
VAR __result =
    MAXX ( __Top1, [@Pop] )
RETURN
    __result

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    cesarvinas , You need to have a new table Year table, Using that try to force the data for the year you do not have the year.

    Can you share sample data and sample output in table format?

    • cesarvinas's avatar
      cesarvinas
      Icon for Advocate I rankAdvocate I
      6 years ago

      amitchandak, don't know how to attach pbix files here, but see screenshots of the example I have below. The question is: how do I combine the Years table with the measure to force the additional 2019 and 2020 years with a value same than the total for 2018?

       

       

       

       

      Measure is: TotalPopulation = SUM('PlacePopulation'[Population])

       

       

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Now that you have your Years table, you could use an expression like this for your population to get the most recent non-blank population

         

        Population =
VAR __thisyear =
    SELECTEDVALUE ( Years[Year] )
VAR __summary =
    ADDCOLUMNS (
        FILTER ( ALL ( Years[Year] ), Years[Year] <= __thisyear ),
        "@Pop", CALCULATE ( SUM ( PlacePopulation[Population] ) )
    )
VAR __Top1 =
    TOPN ( 1, FILTER ( __summary, [@Pop] > 0 ), Years[Year] )
VAR __result =
    MAXX ( __Top1, [@Pop] )
RETURN
    __result

         

        If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat