Profit loss calculation report in Matrix Visual

[Alex_Berlin]

Hi everybody,

We are currently trying to migrate a profit loss calculation report (as multi-level contribution margin accounting) to Power BI. So far, we have succeeded in creating a tabular view in Power BI Desktop (see figure 1) that looks quite similar to the original report.
We use a matrix visual, because it
• offers the ability to group rows together,
• includes subtotals &
• allows for the necessary formatting that is greatly needed by the report’s audience
(i. e. it is very important that we have subtotal rows and can highlight them).

 
Figure 1: Our current version of the table in Power BI using a matrix visual with highlighted subtotal rows (7, 14, 18, …).

 

Figure 2: Target format that we’d like to recreate in Power BI. Note that the rows highlighted in gray are subtotal rows that also factor in all the rows above, e. g. represent a cumulative sum.

However, there still remain at least two issues that, left uncared for, will prevent us from using Power BI for our controlling reports:
• The matrix visualization sums up all sub-totals for each category individually. What we need, however, is a running cumulative sum, but only at the highest level (subtotal rows starting with 7, 14, 18, 22, 32 & 42 in figure 1) starting from the top (see figure 2). If you read the table from top to bottom you should be able to add the values of the rows (ignoring subtotal rows) and when arriving at subtotal rows you will get the cumulative sum up to that very row. The last row should then contain the EBIT (earnings before interest and taxes).
For example in figure 1, the row starting with 14 contains the sum of 10, 12 & 13 which is not what we want. Instead, we would like to have the sum of 10, 12, 13 AND 1, 2, 3 (a cumulative sum so to speak).
• The last two columns in figure 1 should be formatted as a percentage indicating the change between the last year (Vorjahr) and the current year (Ist).

Does anyone have any suggestions on how to tackle these issues? Is there an alternative way for us to (re-)create the aforementioned table (see figure 2) with its formatted subtotal rows and cumulative sums as described above? Feedback would be much appreciated!

Best regards,

Alex

[v-piga-msft]

Hi @Alex_Berlin,

Based on your detail description, I understand that you want to get the output like Figure 2?

However, It's hard for me to reproduce your scenario, because I don't know your table structure.

If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[Alex_Berlin]

Hi Cherry,

yes, the output shoud look like Figure 2.

You will find the pbix and the excel data file under the following link to onedrive:

https://1drv.ms/f/s!Aiwh2CWXTd4jiL8_AzqK7WwMKchjrA

I hope it will be no problem, that everything is in german. If however give me a sign and I will try to translate the wording. Please note, that in Germany Power BI uses the ";" in lieu of the "," in formulas.

Thank you very much in advance! for trying to help me!

Best regards from Berlin, Germany

Alex

[ImkeF]

Hi Alex,

for your specific scenario, you can use a GuV-Measure like this:

GuV = if([ZeilenCat]=[ZeilenContext],[RT],[Act])

where 

ZeilenCat = CALCULATE(COUNTROWS('Kostenart zu DB-Zeile'), FILTER(ALL('Kostenart zu DB-Zeile'), 'Kostenart zu DB-Zeile'[DB Summenzeile ++]=MAX('Kostenart zu DB-Zeile'[DB Summenzeile ++])))

ZeilenContext = COUNTROWS('Kostenart zu DB-Zeile')

Act = SUM('Daten fixiert'[Betrag (korr. VZ)])

RT = IF(NOT(ISBLANK(SUM('Daten fixiert'[Betrag (korr. VZ)]))),CALCULATE(SUM('Daten fixiert'[Betrag (korr. VZ)]), FILTER(ALL('Kostenart zu DB-Zeile'), 'Kostenart zu DB-Zeile'[DB Summenzeile]<=MAX('Kostenart zu DB-Zeile'[DB Summenzeile]))))

So the running total (RT) is used on subtotals and the actual figures (Act) on detail level. The identification if we are on a subtotal or not is determined by a comparison of the amount of rows filtered.

This works fine with one exemption: "Ergebnis der gewöhnlichen Geschäftstätigkeit" (the last item) where there is only one subcategory within the main category. You can easily bypass this by adding a "Dummy-row" with a different value in "DB-Unterzeile" into your account-group-table (so that the number of accounts within the sub-levels are lower than alll accounts on the group-level). (I will try to find out if there are different methods to achieve this that don't require this workaround and post here)

Link to file: https://1drv.ms/u/s!Av_aAl3fXRbehbIatilKWOIHSvoGYA

[ImkeF · Accepted solution]

Coming to think about it, there is actually a simpler solution:

GuV = if([IsDetail],[Act],[RT])

with

IsDetail = ISFILTERED('Kostenart zu DB-Zeile'[Konto ++]) || ISFILTERED('Kostenart zu DB-Zeile'[DB Unterzeile ++]) || ISFILTERED('Kostenart zu DB-Zeile'[DB Zeile ++]) 

The measure "IsDetail" checks if one of the levels/groups that are considered as details is filtered. No need to adjust any tables then 🙂

[Alex_Berlin]

Hi Imke,

thank you very much! This works!

Alex

[ImkeF]

Pleased to hear, so thanks for the feedback @Alex_Berlin 🙂

With regards to the deviation column I strongly recommend to adjust your input data to a "standard model" where you use a calendar table and construct a date-column in your "Daten fixiert". Then you can use the quick measures from the shelve and will be able to find much more help on the internet than with your current model.