Forum Discussion
Matrix visual with difference calculation from total row
I need to create a matrix visual that looks like the following:
| Train | Day 6 | Day 7 | Day 8 |
Loc. A | 2088 | 2204 | 2088 |
| Loc. B | 1508 | 1508 | 2436 |
| Total | 3596 | 3712 | 4524 |
| Capacity | 4000 | 4000 | 4000 |
| Difference | -404 | -288 | 524 |
(train load in tons, capacity is specific to Company A, I have different tables featuring different trains and capacities on a per company basis).
Source data features 2 different tables. One for the load that the train is carrying and one for the companies and their total capacity that follow a simple relationship with eachother:
| Company | Train | Day | Load |
| Company A | Loc. A | 6 | 1392 |
| Company A | Loc. A | 6 | 696 |
| Company A | Loc. B | 6 | 1508 |
| Company B | Loc. A | 6 | 928 |
| Company B | Loc. A | 6 | 1044 |
| Company | Capacity |
| Company A | 4000 |
| Company B | 4200 |
I have been able to create the matrix without the capacity and difference row. But I do need to display the capacity and the difference from the actual planning preferably within one table. Any other ideas are also welcome!
Thank you in advance!
Hi Anonymous ,
Please create a new table.
Table = VAR newcapacity = CROSSJOIN ( Capacity, VALUES ( Train[Day] ) ) VAR UnionTab = UNION ( Train, SELECTCOLUMNS ( newcapacity, "Company", [Company], "Train", "Capacity", "Day", [Day], "Load", [Capacity] ) ) RETURN ADDCOLUMNS ( UnionTab, "Type", IF ( [Train] = "Capacity", "Capacity", "Train" ) )Then create these measures.
Load and Capacity = CALCULATE ( SUM ( 'Table'[Load] ), 'Table'[Company] = "Company A" )TotalValue = VAR diff = CALCULATE ( [Load and Capacity], 'Table'[Type] = "Train" ) - CALCULATE ( [Load and Capacity], 'Table'[Type] = "Capacity" ) RETURN IF ( ISFILTERED ( 'Table'[Type] ), [Load and Capacity], diff )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
Please create a new table.
Table = VAR newcapacity = CROSSJOIN ( Capacity, VALUES ( Train[Day] ) ) VAR UnionTab = UNION ( Train, SELECTCOLUMNS ( newcapacity, "Company", [Company], "Train", "Capacity", "Day", [Day], "Load", [Capacity] ) ) RETURN ADDCOLUMNS ( UnionTab, "Type", IF ( [Train] = "Capacity", "Capacity", "Train" ) )Then create these measures.
Load and Capacity = CALCULATE ( SUM ( 'Table'[Load] ), 'Table'[Company] = "Company A" )TotalValue = VAR diff = CALCULATE ( [Load and Capacity], 'Table'[Type] = "Train" ) - CALCULATE ( [Load and Capacity], 'Table'[Type] = "Capacity" ) RETURN IF ( ISFILTERED ( 'Table'[Type] ), [Load and Capacity], diff )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Anonymous , there is work around suggeted here
Power bi Excel for P&L , add in app
Curbal
https://www.youtube.com/watch?v=IISYzTaIyu4
Guyinacube
https://www.youtube.com/watch?v=1yJnmZRTNZgamitchandak Thank you for your reply.
I watched both videos that suggest adding an Excel workbook to a Power BI app.
Is that the only way to achieve this? I'd like to have the matrix within a report.
