This week I was asked to create a matrix in a Power BI report that looks like this:
To my surprise, Power BI only lets you put multiple values on columns in a matrix. You can’t stack metrics vertically. Note: this is true as of 8 Jan 2016 but may change in the future. If you agree that this should be a feature in Power BI, please make your voice heard and vote for this idea on the Power BI forum and encourage others to vote for it as well.
Please vote for ‘Matrix Measures or Values on Rows instead of Columns’ in Power BI. It is much needed. https://t.co/Du2TyEb0xU
— Meagan Longoria (@mmarie) January 6, 2016
Since I needed to finish this report, I got a little creative with a disconnected table and some DAX to implement a workaround in my tabular model.
The Disconnected Table
I created a table called Row Order in my tabular model using the following SQL Query as a source.
select 1 as [Row Order], 'Lost Customers' as [Customer Metric]
select 2 as [Row Order], 'Inactive Customers' as [Customer Metric]
select 3 as [Row Order], 'New Customers' as [Customer Metric]
I set the Sort By Column property on the Customer Metric column to Row Order and hid the Row Order column.
I already had the base metrics for my matrix:
- Lost Customers – Week (customers considered lost in the current week, based upon the selected date)
- Lost Customers – Month (customers considered lost in the current month, based upon the selected date)
- Lost Customers – Week (customers considered lost in the current month, based upon the selected date)
- Inactive Customers – Week (customers considered to have moved to inactive status in the current week, based upon the selected date)
- Inactive Customers – Month (customers considered to have moved to inactive status in the current month, based upon the selected date)
- Inactive Customers – Quarter (customers considered to have moved to inactive status in the current quarter, based upon the selected date)
- New Customers – Week (customers who have made their first purchase in the current week, based upon the selected date)
- New Customers – Month (customers who have made their first purchase in the current month, based upon the selected date)
- New Customers – Quarter (customers who have made their first purchase in the current quarter, based upon the selected date)
As a side note, when creating time-based measures I prefer to create the measure such that it accepts a date and then filters off of that rather than assuming everyone wants to see the current week/month/quarter. I then add an Is Today field on my date table so if someone does want to see the current week they easily select that in a filter on this field. This means that I can see lost customers for any week, not just the current. Maybe I want to filter my dashboard to the end of December. If I had just created a measure that assumed I wanted last month, I wouldn’t be able to just change my date filter and get the data I want. As another example, lots of people create a metric to calculate sales in the last 6 weeks. But many people assume they want the last 6 weeks from today rather than allowing the user to choose a date and then calculating sales in the 6 weeks leading up to that day. I much prefer the more flexible calculation.
But having this flexibility in my calculations meant that I couldn’t just put some calculated column for time period in my date dimension. And even if I had made the assumption that I wanted to see the data as of today, it would probably be a separate table or a many-to-many relationship (e.g., 5 Jan 2016 would be in the last week, month, and year).
With my new disconnected table added, I just needed to add a few more measures. First, I needed something to check which customer metric I should be showing:
RowOrder:=Max('Row Order'[Row Order])
I hid the RowOrder measure. Then I created one measure for each time frame (week/month/quarter) that would select the correct measure based upon the Customer Metric on that row.
Weekly Customer:=IF(HASONEVALUE(Row Order[Row Order]), SWITCH([RowOrder],1,[Lost Customers - Week], 2,[Inactive Customers - Week], 3,[New Customers - Week],BLANK()), BLANK())
Monthly Customers:=IF(HASONEVALUE(Row Order[Row Order]), SWITCH([RowOrder],1,[Lost Customers - Month], 2,[Inactive Customers - Month], 3,[New Customers - Month],BLANK()), BLANK())
Quarterly Customers:=IF(HASONEVALUE(Row Order[Row Order]), SWITCH([RowOrder],1,[Lost Customers - Quarter], 2,[Inactive Customers - Quarter], 3,[New Customers - Quarter],BLANK()), BLANK())
Power BI Matrix
I opened my report on Power BI.com and created my matrix.
I put Customer Metric in Rows and Weekly Customers, Monthly Customers, and Quarter Customers in Values. Then I filtered my table to As of Today = “As Of Today”.
That gives me the matrix I wanted to create. I don’t love that I had to add the extra table, but it works for now until I can achieve this natively with Power BI.
9 thoughts on “Creating a Matrix in Power BI With Multiple Values on Rows”
I did about 30 minutes of searching before I came across this article. THANK YOU for posting this!! In addition to your table matrix solution, I applied the same logic/process to create a simple “summary of calculated measures” table who’s columns can be dragged into visualizations that otherwise were impossible! Wonderful manual workaround until Power BI can add this type of functionality naively.
Thanks. I’m glad this post was helpful.
@Brad, I too did the “Summary of Calculated Columns” technique but just some of the base measures have different formatting eg some values and some %. Think finance P&L report where SAles & contribution are USD but Gross MArgin is a %.
How would you maintain the individual formatting of the separate ‘base’ measures now they are displayed as ‘one’ measure type as in Meagan’s example the “Weekly Customer” summary measure is displaying one type of her base measures [Lost Customers – Week], [Inactive Customers – Week], [New Customers – Week] ?
@David, You can use the FORMAT() function to help with this. For instance, if I have a value that could be revenue, margin $, and margin %. It might be
Value = IF(HASONEVALUE(‘Measures Selection'[Row Order]), SWITCH([RowOrder],1,Format([Total Revenue],”Currency”),2,Format([Total Margin],”Currency”),3,Format([Margin %],”Percent”)))
Be aware that the resulting measure is returned as a string with the appropriate formatting. This works fine in a table, but you might run into an issue if you are ever bui;ding something that expects a number rather than a string.
@Meagan thats exactly what i wanted. thanks. Following your link and going further to the custom formats @ https://msdn.microsoft.com/en-us/library/ee634206.aspx I ended up using
That formatting is really similar to excel custom number formatting rules. Just put the rule inside the double quotes as above.
This post was awesome, thanx.
Thank you for sharing this method. I was looking for something similar to do in a Matrix and none of the standard or custom widgets were able to. This is neat and clean!
I am using a similar disconnected table for filtered charts, where I create a filter with the Custom Metric and switch the X value on a column or line chart.