Forum Discussion
MATRIX - Seperating a column
Hi,
I have a dataset as below. The total is calculated and is a column in the dataset. I'm using a MATRIX in my report
| Home Expense | |
| Rent | 59 |
| Electricity | 100 |
| Grocery | 300 |
| Total | 459 |
| Commodities | |
| Vegetables | 30 |
| Meat | 10 |
| Fruits | 20 |
| Total | 60 |
How can I get the total row with a border line at the top and bottom?
5 Replies
Anonymous , this final display or your raw data. If it is raw data it needs some cleanup.
But, if it final display, you can use matrix
https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/
This is my raw data. What do I need to cleanup?
Anonymous , are these two data set. or one. Is data available like this
Home Expense Rent 59 Electricity 100 Grocery 300 Total 459 Commodities Vegetables 30 Meat 10 Fruits 20 Total 60 If like above https://community.powerbi.com/t5/Desktop/Fill-empty-cells-based-on-criteria/td-p/101190
if two data set I will create a common dimension having both home expense type and commodities and join it both table and create a measure to sum from both tables
New Dim = distinct(union(all(Table1[home expense type]),Table2[commodities ])))
Measure = sum(Table1[Value])+sum(Table2[Value])
Hi Anonymous ,
You don't have total row in your raw table right?
Refer the formula to create a new table as below.
Table 2 = UNION('Table',DISTINCT(SELECTCOLUMNS('Table',"type",'Table'[type],"category","total","value",CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[type])))))Then create a measure and use it as value field in your visual.
Measure = MAX('Table 2'[value])Result would be shown as below.
Best Regards,
Jay
