Forum Discussion

johnnywj's avatar
johnnywj
Frequent Visitor
8 years ago
Solved

Table Layout - Measures on row and column

Hello all,

 

I'm trying to get the layout of a matrix to work and need some advice. I have a matrix that shows several measures across years (measures 1-4), but I'd like to be able to add an additional measure to the column after the years. Measure 5 will be a CAGR calculation (compounded annual growth rate) and will use values from the years. The formula for CAGR = ((2018 value) / (2015 value) ) ^3 -1.

 

I already have the CAGR measure created, I'm just not able to load it into the table. By the way, I'm using 'Show values on Rows' = On.

 

 

A couple of thoughts and downfalls I've had so far to solve this:

  • Create a card with the CAGR measure
    • Hard to align with original table
    • Would need to create lots of measures
  • Use R to create a custom table output
  • Somehow create an intermediate table of the above information (without measure 5) and then create a new second table
    • Not sure if this would actually work or how to do it

Really appreciate any advice!

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi johnnywj,

     

    I don't think this matrix structure is achievable. Since you have set "Show values on Rows" to "On", all measures will be displayed in row, so, it doesn't make sense to display Measure5 as column. Besides, in a matrix, it is not able to show "Measure5" as a column header align with 2015, 2016 ect. When we add a measure into matrix, it will be displayed under the top level column headers, act as a child column header.

     

    Regards,

    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi johnnywj,

     

    I don't think this matrix structure is achievable. Since you have set "Show values on Rows" to "On", all measures will be displayed in row, so, it doesn't make sense to display Measure5 as column. Besides, in a matrix, it is not able to show "Measure5" as a column header align with 2015, 2016 ect. When we add a measure into matrix, it will be displayed under the top level column headers, act as a child column header.

     

    Regards,

    Yuliana Gu

    • johnnywj's avatar
      johnnywj
      Frequent Visitor
      8 years ago

      Thanks for the reply. I ended up creating small cards to hold individual values to create a fake column to hold measure #5. I know powerBI isn't a full reporting tool yet, but it seems odd to not be able to create tables of data in whatever format I'd want. Especially when the measure itself worked and didn't need to be hacked to make sense. It's impossible to explain to customers/managers that our awesome tool PowerBI that we rave about can't do something that people have been doing in old tools for a decade.