Forum Discussion
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.
I am not able to put the measure in the column field. So I dropped it in the value field.
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:
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 columnWere 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.
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:
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.
9 Replies
I Was just asking the same exact Question, Hope we get a reply !
- Cmcmahan
Resident Rockstar
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:
Level3Name Custom DN-Up Assets Param1 100 Assets Param2 50 Assets Param3 60 Assets Param4 90 Liabilities Param1 200 Liabilities Param2 100 Liabilities Param3 300 Liabilities Param4 100 Using that data, I just put it into a matrix visual, and got this:
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:
- Cmcmahan
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.
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:
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.
