Forum Discussion
Adding multiple columns in matrix view
I need to create a matrix table like below. I have metrics 1,2,3 and Week field with all the weeks. How do I separate into the current, prior week etc and get MoM, YoY, WoW in the same matrix? This can be easily done in excel but I couldn't find a way to power bi
| Current week(Measure) | Prior week(Measure) | Same week last month(Measure) | Same week last year(Measure) | Month on Month(Measure) | Year on Year(Measure) | Week on week(Measure) | |
| Sales (Measure) | |||||||
| Profit(Measure) | |||||||
| Tax (Measure) |
|
11 Replies
Hello pranesh246 ,
Looks like you need to create measure with help of DAX for all the required columns and pull them individulally one by one.
You can use time intelligence functions from below link:
https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax
Thanks!
Hi Shishir22 ,
Unfortunately, I am able to add measures only as a row in matrix but the requirement is to add current, prior week, MoM, YoY, WoW measures in columns .
Hello pranesh246 ,
Can you please try dragging all the measure one by one in Values section of the matrix?
It should give you requried output.
Thanks!
Not sure why this isn't working for you. You should be able to just put the measures into the values well and they should display in the columns.
can you just use a table instead of matrix? Then they have to be in the columns.
screenshot here shows the measures similar to what you want in columns.
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-tablesHi Carmichael
The ones in the first column are different measures. I need values in the corresponding cells based on row measure and column measure.
Current week(Measure) Prior week(Measure) Same week last month(Measure) Same week last year(Measure) Month on Month(Measure) Year on Year(Measure) Week on week(Measure) Sales (Measure) Profit(Measure) Ah - ok....so Measures in both Rows and Columns. Thanks for clarification and for updating your question to make that clear to others.
I would be trying to find a way to get the row measures into calculated columns but that purley because of I have no answer for rows in both Columns and Rows.
I am interested in learning/understanding this myself so I did a quick search and found a few answers all using some variant of switch. The following post has a solution using calculated tables (Cross Join on second solution). Hopefully it might give you an idea to further research.
Please post back here if you do get a slution working for you.
https://community.powerbi.com/t5/Desktop/Table-with-multiple-measures/td-p/1047793
Hi pranesh246
It seems that you want to show your measures names in column and row at the same time.
Currently you could only add your measures in value in Matrix.
And power bi doesn't support you to add measures in column and row in matrix directly.
Your measure name will show in column by default.
If you want to show your measure name in row, you could turn on Show on rows in value in Format.
For more details about building a matrix: Create matrix visualizations in Power BI
Your demand is a good idea, while it is not supported to implement in Power BI currently.
You can vote up this idea for this function：to be able to add measures on rows and columns at the same matrixOr you can submit a new idea to improve the Power BI.
It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi pranesh246
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Here is a thread which covers how to create a matrix with measures for rows and columns:
