Forum Discussion

Soc3's avatar
Soc3
Helper I
1 year ago
Solved

Matrix Table- Single Column Value and Previous Year Measure

I have names in the rows (hidden for privacy purposes), dates in the columns and percentages in the values (a measure). I want to add a single column that states what the target is.

 

 

 

How do I add a column that states the target in each row, 78%, without repeating for each month? I only need it to display in one column rather than repeat for each month column.

 

I would also like to add a column that summarizes the previous year's YE average %.  To do that, I created a measure similar to the measure used in the table shown above but added the PREVIOUSYEAR function. No data returns. Any suggestions on how to achieve this? Issue Year is in date format. Similar to the target value, I would only want to display the previous year % to as a single column.

 

This is what I tried:

% Previous YR = CALCULATE(DIVIDE([Count of Ticket], [Row Total]), PREVIOUSYEAR('V02_TICKET'[Issue Year]))

 

The dashboard has dynamic row level security and the matrix has the ability to drill down based on the org chart, so I need the values to be measures so they can calculate for each level.

  • Hi Soc3 

     

    If you need the Target to appear next to the row value only once, you can create a calculated column with the target and use it as a row, then change the matrix table layout style to Tabular.

     

     

10 Replies

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support

    Hi Soc3,

    Thank you for reaching out to the Microsoft fabric community forum.

    I have recreated your scenario using sample data. Please find the attached PBIX file for your reference.

    Hope this helps.

     

    Thank you.

    • Soc3's avatar
      Soc3
      Helper I

      Hi v-saisrao-msft ,

       

      Thanks for your response and the PBI file. This won't work in my scenario due to the drill down of organizational hierarchy in the rows. I have level 2, level 3, level 4, etc to allow leadership to drill through the metrics for each manager. Is there a way to achieve this in columns?

      • v-saisrao-msft's avatar
        v-saisrao-msft
        Community Support

        Hi Soc3,

        Please try moving the issue year field from the rows section to the columns section. See the screenshot below for your reference.

        I hope this resolves your issue.

         

        Thank you.

  • Hi Soc3 

     

    If you need the Target to appear next to the row value only once, you can create a calculated column with the target and use it as a row, then change the matrix table layout style to Tabular.

     

     

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support

    Hi Soc3,

    Checking in to see if your issue has been resolved. let us know if you still need any assistance.

     

    Thank you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support

    Hi Soc3,

    Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

     

    Thank you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support

    Hi Soc3,

    We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

     

    Thank you.