Forum Discussion

smcgvert's avatar
smcgvert
Regular Visitor
3 years ago
Solved

Creating calculation rows within P&L Matrix

Good Afternoon All,

 

I am building a P&L within Power BI and am having difficulty inserting calculated rows (for Gross Margin, Gross Margin % etc).

 

Below is an example of where I'm up to: 

9 measures have been created, MTH, QTD and YTD for Actual, Budget and Prior Year - as below:

1 MTH Actual = SUM('3 Mapping table + 2'[Amount (budget rate)])
1 QTD Actual = CALCULATE(Sum('3 Mapping table + 2'[Amount (budget rate)]),DATESQTD('Dates 2'[Date]))
1 YTD Actual = CALCULATE(Sum('3 Mapping table + 2'[Amount (budget rate)]),DATESYTD('Dates 2'[Date]))
2 MTH Prior Year = CALCULATE([1 MTH Actual],SAMEPERIODLASTYEAR('Dates 2'[Date]))
2 QTD Prior Year = CALCULATE([1 QTD Actual],SAMEPERIODLASTYEAR('Dates 2'[Date]))
2 YTD Prior Year = CALCULATE([1 YTD Actual],SAMEPERIODLASTYEAR('Dates 2'[Date]))
1 MTH Budget = SUM('Budget figures'[Amount])
1 QTD Budget = CALCULATE(Sum('Budget figures'[Amount]),DATESQTD('Dates 2'[Date]))
1 YTD Budget = CALCULATE(Sum('Budget figures'[Amount]),DATESYTD('Dates 2'[Date]))
 
These measures are then combined in a 'SWITCH TABLE' to allow me to switch between MTH, QTD and YTD, as below - 
Act Select = SWITCH([Selected SWITCH Periodic YTD],1,[1 MTH Actual],2,[1 QTD Actual],3,[1 YTD Actual])
Bud Select = SWITCH([Selected SWITCH Periodic YTD],1,[1 MTH Budget],2,[1 QTD Budget],3,[1 YTD Budget])
PY Select = SWITCH([Selected SWITCH Periodic YTD],1,[2 MTH Prior Year],2,[2 QTD Prior Year],3,[2 YTD Prior Year])
 
I'd like to be able to total the Revenue, COGS and OPEX elements of the P&L and add these totals in to the appropriate line in the P&L, as well as Gross Margin % and EBITDA % calculations.
 
I do have my "Rollup" accounts mapped to the below Groups, but can not figure out how to create gross margin, OPEX and EBITDA calculations. Any help would be very much appreciated.