Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculated Column inside Matrix where measures are rows

 Hi, I have a list of many Measures by Year, in the Matrix, I had the Values "show on rows" so that the measures are in rows, and the Years are Columns.  Now I want to take YOY comparisons of the Years, is this possible? So I want to take YOY comparisons by Number and % increases by year, on the measures that I have in rows.  I having been trying many ways for hours but nothing is working. I was wondering if it's even possible?

Need Help

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous

    You can use SAMEPERIODLASTYEAR Function to add a last year total measure like below:

    last year total = CALCULATE(SUM(Table1[Sales]),SAMEPERIODLASTYEAR('Date'[Date]))

    And YOY % increase measure

    YOY% = DIVIDE([total]-[last year total],[last year total])

    Also you can use New quick measure to quickly add a measure

    then drag these measure into Value

    Result:

     

    Best Regards,

    Lin

     

     

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Lin,

       

      Yes, I got that to work just fine when I have measures totaling each year (2015-2018) and YOY changes in dollars and %'s for P&L & Sales items (coming off one column of values in the data coming from Excel).  But now I am doing another chart where I am taking KPI measures/functions off that same P&L data, then showing those functions as rows (2015-2018) (through the values feature "show on rows") and now I want calculated columns for YOY changes going across, on those measures.  I think my issue is that if I have 20 KPI Measures/DAX Formulas, and then I put them into rows, it seems I have to create measures for each year on those measures for each YOY Changes (2016-2018),  What I did in the meantime was create a ton of measures, then make multiple Matrixes for each YOY Change and then sort of hide the row headings and line all the tables up.  I know I could use cards instead, but that would slow down the dashboard.  I know this sounds confusing.... I will try what you wrote below, I just don't think it's going to work, but I will let you know!