Forum Discussion
Reorganizing Columns in Matrix
Hello,
I am new to developing in Power BI and am trying to replicate, as best as i can, a previous report. The report shows Revenue by quarter for two differnent time periords, Previuous Year and Curreny Year. the report then shows dollar difference and % change by quarter. However, when I set this up in a matrix, the output looks like:
| Q1 | Q2 | Q3 | Q4 | |||||||||||||
| PY Rev | CY Rev | $ ▲ | % ▲ | PY Rev | CY Rev | $ ▲ | % ▲ | PY Rev | CY Rev | $ ▲ | % ▲ | PY Rev | CY Rev | $ ▲ | % ▲ | |
| McDonalds | ||||||||||||||||
| Wendy's |
I want the report to be set up like this:
| Q1 | Q2 | Q3 | Q4 | Q1 | Q2 | Q3 | Q4 | Q1 | Q2 | Q3 | Q4 | Q1 | Q2 | Q3 | Q4 | |
| PY Rev | PY Rev | PY Rev | PY Rev | CY Rev | CY Rev | CY Rev | CY Rev | $ ▲ Q/Q | $ ▲ Q/Q | $ ▲ Q/Q | $ ▲ Q/Q | % ▲ Q/Q | % ▲ Q/Q | % ▲ Q/Q | % ▲ Q/Q | |
| McDonalds | ||||||||||||||||
| Wendy's |
Is there a way to reorder columns in Power BI, or does anyone have suggestions on how to present this report? Thanks!
1 Reply
Use 'Enter data' to create a little 4-row 2-column table. Call the table 'tblColumns':
Col_Name Order
CY Rev 1
PY Rev 2
$ Q/Q 3
% Q/Q 4
Once created, click on [Col_Name] and a 'Column Tools' tab should appear in the ribbon. Use 'Sort by column' to sort [Col_Name] by [Order]
Ensure this little table is NOT linked to anything in modelling view.
Create the following measure:
tableVales = if(SELECTEDVALUE(tblColumns[Col_Name]) = "CY Rev", [CY Rev],if(SELECTEDVALUE(tblColumns[Col_Name]) = "PY Rev", [PY Rev],if(SELECTEDVALUE(tblColumns[Col_Name]) = "$ Q/Q", [$ Q/Q],if(SELECTEDVALUE(tblColumns[Col_Name]) = "% Q/Q", [% Q/Q],0))))I have assumed you already have [CY Rev], [PY Rev], [$ Q/Q], and [% Q/Q] measures from your previous work (or, if not, they can easily be made).Create a matrix visual. [Business] goes in rows, [Col_Name] then [Quarter] in columns, [tableValues] in values.I realise some things are confusingly named, but what else do I call a table which lists names of measures?
