Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Referencing Previous Column and Row using Measures in Power Pivot

Hello, I am currently trying to create a measure (in Power Pivot and Power Query) that will allow me to reference a different row and column from the one I am currently working on (see question at v...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

Measures in a matrix visual are recalculated for each individual cell, for the row totals, the column totals, and for the grand total.

 

You can do this kind of cumulative calculation, but you have to do it independently for each cell. You cannot reference visual cells in measures.

 

General guidance: "Think like the Grand Total" . More often than not a formula that works for the grand total will also work for the subtotals and individual cells.

 

If you like more help please provide sample data in usable format (not as a picture - maybe insert into a table?).

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello lbendlin ,

    Thank you for your help and for the explaination! 

     

    I have updated my spreadsheet to reflect the new information. (Please see below screenshot).

     

    Also, by "cumulative calculation," do you mean something like the EARLIER function? That is how I have set up my measures so far.

     

    Once again, I would appreciate your help and any feedback you can provide on how to update my current measures!

    Thanks!

     

     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      5 years ago

      The use of EARLIER is discouraged. Instead, use variables to protect your results from filter context transitions.

       

      But - if it works for you then go with it.