Matrix visual with difference calculation from total row

[Anonymous]

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!

[v-kkf-msft · Accepted solution]

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.

[amitchandak]

@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=1yJnmZRTNZg

[Anonymous]

@amitchandak 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.