cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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!

1 ACCEPTED SOLUTION
Community Support

Hi @d_h ,

``````Table =
VAR newcapacity =
CROSSJOIN ( Capacity, VALUES ( Train[Day] ) )
VAR UnionTab =
UNION (
Train,
SELECTCOLUMNS (
newcapacity,
"Company", [Company],
"Train", "Capacity",
"Day", [Day],
)
)
RETURN
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 3
Community Support

Hi @d_h ,

``````Table =
VAR newcapacity =
CROSSJOIN ( Capacity, VALUES ( Train[Day] ) )
VAR UnionTab =
UNION (
Train,
SELECTCOLUMNS (
newcapacity,
"Company", [Company],
"Train", "Capacity",
"Day", [Day],
)
)
RETURN
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.

Super User

@d_h , there is work around suggeted here

Power bi Excel for P&L , add in app
Curbal
Guyinacube

Regular Visitor

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.