Add row in dashboard table

[frankhofmans]

hi PBI experts,

I have the following question: i have a data table with P&L data. The structure is as following:

Period	P&L level	Value	Reporting level
2021-1	Net Sales	1500000	Actuals
2021-1	COS	-650000	Actuals
2021-1	Direct Costs	-500000	Actuals
2021-1	Other operating expenses	-265000	Actuals
2021-1	Depr. & Am.	-25000	Actuals
2021-2	Net Sales	1800000	Actuals
2021-2	COS	-700000	Actuals
2021-2	Direct Costs	-600000	Actuals
2021-2	Other operating expenses	-270000	Actuals
2021-2	Depr. & Am.	-25000	Actuals
2021-3	Net Sales	2000000	Actuals
2021-3	COS	-800000	Actuals
2021-3	Direct Costs	-700000	Actuals
2021-3	Other operating expenses	-300000	Actuals
2021-3	Depr. & Am.	-26000	Actuals
2021-1	Net Sales	1600000	Budget
2021-1	COS	-700000	Budget
2021-1	Direct Costs	-480000	Budget
2021-1	Other operating expenses	-265000	Budget
2021-1	Depr. & Am.	-25000	Budget
2021-2	Net Sales	1700000	Budget
2021-2	COS	-675000	Budget
2021-2	Direct Costs	-575000	Budget
2021-2	Other operating expenses	-250000	Budget
2021-2	Depr. & Am.	-25000	Budget
2021-3	Net Sales	2100000	Budget
2021-3	COS	-830000	Budget
2021-3	Direct Costs	-720000	Budget
2021-3	Other operating expenses	-290000	Budget
2021-3	Depr. & Am.	-27000	Budget

I want to create a P&L in the dashboard, but with Gross Margin (Net Sales + COS), Gross Profit (Net Sales + COS + Direct Costs), EBITDA (Net Sales + COS + Direct Costs + Other operating expenses) and EBIT (Net Sales + COS + Direct Costs + Other operating expenses + Depr. & Am.). This should result in the following P&L:

	Actuals	Budget	Actuals vs Budget variance
Net Sales
Cos
Gross Margin
Direct Costs
Gross Profit
Other Operating Expenses
EBITDA
Depr. & Am.
EBIT

Who can help me with the solution?

Many thanks in advance,

Regards, Frank

[Anonymous]

Hi @frankhofmans 

Try to build a New P&L level Table by Dax then build measures as below.

Matrix Table = 
VAR _NewPLlevel =
    UNION (
        VALUES ( 'Table'[P&L level] ),
        { "Gross Margin", "Gross Profit", "EBITDA", "EBIT" }
    )
VAR _AddSort =
    ADDCOLUMNS (
        _NewPLlevel,
        "PLsort",
            SWITCH (
                [P&L level],
                "Net Sales", 1,
                "COS", 2,
                "Direct Costs", 3,
                "Other operating expenses", 4,
                "Depr. & Am.", 5,
                "Gross Margin", 6,
                "Gross Profit", 7,
                "EBITDA", 8,
                9
            )
    )
RETURN
    _AddSort

Table:

We can sort New P&L level column by PLsort column.

Measures:

Actuals = 
VAR _CurrentPL =
    MAX ( 'Matrix Table'[P&L level] )
VAR _INRANGEAct =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level] = MAX ( 'Matrix Table'[P&L level] )
                && 'Table'[Reporting level] = "Actuals"
        )
    )
VAR _GMAct =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN { "Net Sales", "COS" }
                && 'Table'[Reporting level] = "Actuals"
        )
    )
VAR _GPAct =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN { "Net Sales", "COS", "Direct Costs" }
                && 'Table'[Reporting level] = "Actuals"
        )
    )
VAR _EBITDAAct =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN { "Net Sales", "COS", "Direct Costs", "Other operating expenses" }
                && 'Table'[Reporting level] = "Actuals"
        )
    )
VAR _EBITAct =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN {
                "Net Sales",
                "COS",
                "Direct Costs",
                "Other operating expenses",
                "Depr. & Am."
            }
                && 'Table'[Reporting level] = "Actuals"
        )
    )
RETURN
    SWITCH (
        TRUE (),
        _CurrentPL IN VALUES ( 'Table'[P&L level] ), _INRANGEAct,
        _CurrentPL = "Gross Margin", _GMAct,
        _CurrentPL = "Gross Profit", _GPAct,
        _CurrentPL = "EBITDA", _EBITDAAct,
        _CurrentPL = "EBIT", _EBITAct
    )

Budget = 
VAR _CurrentPL =
    MAX ( 'Matrix Table'[P&L level] )
VAR _INRANGEBud =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level] = MAX ( 'Matrix Table'[P&L level] )
                && 'Table'[Reporting level] = "Budget"
        )
    )
VAR _GMBud=
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN { "Net Sales", "COS" }
                && 'Table'[Reporting level] = "Budget"
        )
    )
VAR _GPBud =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN { "Net Sales", "COS", "Direct Costs" }
                && 'Table'[Reporting level] = "Budget"
        )
    )
VAR _EBITDABud =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN { "Net Sales", "COS", "Direct Costs", "Other operating expenses" }
                && 'Table'[Reporting level] = "Budget"
        )
    )
VAR _EBITBud =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN {
                "Net Sales",
                "COS",
                "Direct Costs",
                "Other operating expenses",
                "Depr. & Am."
            }
                && 'Table'[Reporting level] = "Budget"
        )
    )
RETURN
    SWITCH (
        TRUE (),
        _CurrentPL IN VALUES ( 'Table'[P&L level] ), _INRANGEBud,
        _CurrentPL = "Gross Margin", _GMBud,
        _CurrentPL = "Gross Profit", _GPBud,
        _CurrentPL = "EBITDA", _EBITDABud,
        _CurrentPL = "EBIT", _EBITBud
    )

Actuals vs Budget variance = [Actuals] - [Budget]

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[frankhofmans]

hi Rico,

This works for me. Many thanks! is it also possible to add Gross Margin % (Gross Margin / Net Sales) under Gross Margin, Gross Profit % under Gross Profit and EBITDA % under EBITDA?

Regards,

Frank

[Anonymous · Accepted solution]

Hi @frankhofmans 

I think this is not a good idea to add Gross Margin %, Gross Profit %, EBITDA % which are in Percentage format into original result. We can see that original result is in whole number format. In Power BI, one measure or column can have only one format. So if you want to add % in your original result, you will need to change the measure format to text, or you have to show Gross Margin %, Gross Profit %, EBITDA % in decimal number format instead of % format. Neither of them are good ideas. 

Here I add Gross Margin % in origianl code.

Matrix Table = 
VAR _NewPLlevel =
    UNION (
        VALUES ( 'Table'[P&L level] ),
        { "Gross Margin","Gross Margin %","Gross Profit","EBITDA", "EBIT" }
    )
VAR _AddSort =
    ADDCOLUMNS (
        _NewPLlevel,
        "PLsort",
            SWITCH (
                [P&L level],
                "Net Sales", 1,
                "COS", 2,
                "Direct Costs", 3,
                "Other operating expenses", 4,
                "Depr. & Am.", 5,
                "Gross Margin", 6,
                "Gross Margin %",7,
                "Gross Profit", 8,
                "EBITDA", 9,
                10
            )
    )
RETURN
    _AddSort

And I create a new Actual measure as a example.

Actuals = 
VAR _CurrentPL =
    MAX ( 'Matrix Table'[P&L level] )
VAR _INRANGEAct =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level] = MAX ( 'Matrix Table'[P&L level] )
                && 'Table'[Reporting level] = "Actuals"
        )
    )
VAR _GMAct =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN { "Net Sales", "COS" }
                && 'Table'[Reporting level] = "Actuals"
        )
    )
VAR _NetSales =     
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level] = "Net Sales"
                && 'Table'[Reporting level] = "Actuals"
        )
    )
VAR _GMActPercent = DIVIDE(_GMAct,_NetSales)
VAR _GPAct =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN { "Net Sales", "COS", "Direct Costs" }
                && 'Table'[Reporting level] = "Actuals"
        )
    )
VAR _EBITDAAct =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN { "Net Sales", "COS", "Direct Costs", "Other operating expenses" }
                && 'Table'[Reporting level] = "Actuals"
        )
    )
VAR _EBITAct =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[P&L level]
                IN {
                "Net Sales",
                "COS",
                "Direct Costs",
                "Other operating expenses",
                "Depr. & Am."
            }
                && 'Table'[Reporting level] = "Actuals"
        )
    )
RETURN
    SWITCH (
        TRUE (),
        _CurrentPL IN VALUES ( 'Table'[P&L level] ), _INRANGEAct,
        _CurrentPL = "Gross Margin", _GMAct,
        _CurrentPL = "Gross Margin %",_GMActPercent,
        _CurrentPL = "Gross Profit", _GPAct,
        _CurrentPL = "EBITDA", _EBITDAAct,
        _CurrentPL = "EBIT", _EBITAct
    )

You see I need to change the measure format to decimal number. If you want to change measure to text, it is complex to update your code.

Here I suggest you to build a new Matrix Table2 with above code.

Matrix Table 2 = 
VAR _NewPLlevel =
        {"Gross Margin %","Gross Profit%","EBITDA%", "EBIT%" }
VAR _AddSort =
    ADDCOLUMNS (
        _NewPLlevel,
        "PLsort",
            SWITCH (
                [Value],
                "Gross Margin %", 1,
                "Gross Profit%", 2,
                "EBITDA%", 3,
                "EBIT%", 4,
                5  
            )
    )
RETURN
    _AddSort

Then you can refer to above measure to create a new measure to show Gross Margin %, Gross Profit %, EBITDA % in the new Matrix. Then you can set your measure format as percentage. 

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@frankhofmans Just create each of those as measures and then use a matrix visual and use the "show on rows" feature to show your measures on each row. The other approach would be to create a disconnected table with your categories and use an additional "measure to show" measure. That is the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...