cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ameyabhave Regular Visitor
Regular Visitor

Display a measure as a column in matrix visual

I want to add a meaure as a column in a matrix visual in the following form:

The column Sum is the measure that sums all the values in the next columns.

 

Photo8.png

 

I am not able to put the measure in the column field. So I dropped it in the value field.

Photo9.png

DN-UP is the value to be displayed in all the remaining columns (it is a calculated column) and sum_all is the measure I am trying to display.

However, I am getting the table in the following format:

Photo10.png

 

The formula I have used for the measure is as follows:

SUMX('Table1','Table1'[DN-UP])
Kindly tell me how I would be able to display the measure in form of a column
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Display a measure as a column in matrix visual

Were my assumptions about how your data is stored correct?  If not, could you share how it is stored?  Also, as a side note, you CAN change the label on the matrix column to say something other than Total, if that's the issue you have with the previous solution.  Check the subtotal formatting options.

 

The Quick and Dirty Method

I'm not sure why you need it as a measure instead of as the total from the matrix, but we can do that.  However, you'll have to get a bit funky with how your data is set up then.  Essentially, what you want is a bogus 5th category that is "Row total" for each Level3Type with blank data, that you then use a measure to change the cell's calculation method depending on your category.

snipa.PNG

And then I created a few measures.  One that summed all values within a Level 3 Type, one that acted as a switch to display either the sum or the row sum, depending on the context:

Sum Row = CALCULATE(SUM(Table3[DN-Up]), ALLEXCEPT(Table3, Table3[Level3Type]))
DN-Up Matrix Sum = IF(SELECTEDVALUE(Table3[Custom])="TheMeasure",[Sum Row], SUM(Table3[DN-Up]))

And with that you get this result:

snipb.PNG

 

Which gets you the result you want, but adding garbage rows to your data just for a visual is a really dumb way to do things. 

 

The Better Way

Instead, you can add a new calculated table that lists out each of the Custom types and manually add your special row summing column name as a value here. Relate that to your original table and use that as your matrix columns instead, along with "Show items with no data" to display the matrix column. 

You have to make minor edits to the IF logic measure to have it check the new table's SELECTEDVALUE.  The resulting matrix looks the same, but it's a LOT cleaner from the data management perspective. 

 

If you need help with the table creation DAX expression, or if this doesn't make sense to you, please ask more questions.  What you're doing sounds easy at first, but doing it right takes a bit of effort.

8 REPLIES 8
Aessam Frequent Visitor
Frequent Visitor

Re: Display a measure as a column in matrix visual

I Was just asking the same exact Question, Hope we get a reply !

Super User
Super User

Re: Display a measure as a column in matrix visual

So a lot of this depends on how your tables are set up.  If your tables are set up like your excel sheet, it won't work very well, but it looks like from the images you took of the visual options, that it's set up more like this:

 

Level3NameCustomDN-Up
AssetsParam1100
AssetsParam250
AssetsParam360
AssetsParam490
LiabilitiesParam1200
LiabilitiesParam2100
LiabilitiesParam3300
LiabilitiesParam4100

 

Using that data, I just put it into a matrix visual, and got this:

snipa.PNG

 

If you're actually looking for the 300 value to be repeated in every category, you would change your measure like this:

Sum all = CALCULATE(SUM(Table3[DN-Up]), FILTER(ALLEXCEPT(Table3, Table3[Level3Type]),TRUE()))

and add it as a value in the table:

snipb.PNG

 

 

 

ameyabhave Regular Visitor
Regular Visitor

Re: Display a measure as a column in matrix visual

I want a column similar to the total column in your 1st table. I just want it to be in form of a measure. Also I want the column name to be different .

Super User
Super User

Re: Display a measure as a column in matrix visual

Were my assumptions about how your data is stored correct?  If not, could you share how it is stored?  Also, as a side note, you CAN change the label on the matrix column to say something other than Total, if that's the issue you have with the previous solution.  Check the subtotal formatting options.

 

The Quick and Dirty Method

I'm not sure why you need it as a measure instead of as the total from the matrix, but we can do that.  However, you'll have to get a bit funky with how your data is set up then.  Essentially, what you want is a bogus 5th category that is "Row total" for each Level3Type with blank data, that you then use a measure to change the cell's calculation method depending on your category.

snipa.PNG

And then I created a few measures.  One that summed all values within a Level 3 Type, one that acted as a switch to display either the sum or the row sum, depending on the context:

Sum Row = CALCULATE(SUM(Table3[DN-Up]), ALLEXCEPT(Table3, Table3[Level3Type]))
DN-Up Matrix Sum = IF(SELECTEDVALUE(Table3[Custom])="TheMeasure",[Sum Row], SUM(Table3[DN-Up]))

And with that you get this result:

snipb.PNG

 

Which gets you the result you want, but adding garbage rows to your data just for a visual is a really dumb way to do things. 

 

The Better Way

Instead, you can add a new calculated table that lists out each of the Custom types and manually add your special row summing column name as a value here. Relate that to your original table and use that as your matrix columns instead, along with "Show items with no data" to display the matrix column. 

You have to make minor edits to the IF logic measure to have it check the new table's SELECTEDVALUE.  The resulting matrix looks the same, but it's a LOT cleaner from the data management perspective. 

 

If you need help with the table creation DAX expression, or if this doesn't make sense to you, please ask more questions.  What you're doing sounds easy at first, but doing it right takes a bit of effort.

ameyabhave Regular Visitor
Regular Visitor

Re: Display a measure as a column in matrix visual

Hi Cmcmahan.

Thank you for your quick reply.

Yes, the table is arranged in the same way.

I will try out both the methods you have described and update the post.

It is definately much more complicated than it appears the first time. 

Super User
Super User

Re: Display a measure as a column in matrix visual

The big issue that makes it difficult is how the PowerBI matrix is set up to figure out what the values should be for rows/columns at any given point. 

 

When you add a field to the column bucket, it iterates through all the values of that field to create each column.  It's essentially calling VALUES(Table[Column]) to determine what is available. When all your desired values are in one column of data, this is quick and easy.  Unfortunately, there's no easy way to add an extra single column of data to the matrix, since you can't add measures as a column.  Measures don't have multiple values to iterate through, they just evaluate differently depending on context.

 

The bad way just goes back and adds that data to the original table, where the good way creates a new table that lists the desired column names and links them to the orginal table.  The matrix then iterates through all the possible values in the table to determine the columns that should be shown.  The measure figures out when it's being evaluated in a normal category through table relationships vs when it's being evaluated and the category is the special "row sum" category.

ameyabhave Regular Visitor
Regular Visitor

Re: Display a measure as a column in matrix visual

The new calculated table would be in this format right?

Photo11.png

 

How do I add another row to a custom column in the calculated table?

The calculated table must be created using ADDCOLUMNS or SUMMARIZE ?

 

Super User
Super User

Re: Display a measure as a column in matrix visual

The calculated table looks like this:

IndexCustomVal
0Parameter1
1Parameter2
2Parameter3
3Parameter4
4Sum_all

 

And the index isn't entirely necessary. I used it to sort the order of the columns in the matrix.  You can create the required column with this dax:

Table 2 = UNION(VALUES(Table3[Custom]), {"Sum_all"})

VALUES returns a table of distinct values in [Custom] and {} defines a new table, in this case with one value "Sum_all". UNION appends the columns together.

 

You can use GENERATE_SERIES when adding a column to create an index if that will be helpful for you.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 116 members 1,585 guests
Please welcome our newest community members: