Forum Discussion

Alex_Berlin's avatar
Alex_Berlin
Advocate I
8 years ago
Solved

Profit loss calculation report in Matrix Visual

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...
Need Help
  • ImkeF's avatar
    ImkeF
    8 years ago

    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 :)

ImkeF's avatar
ImkeF
Community Champion
8 years ago

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's avatar
ImkeF
Community Champion
8 years ago

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's avatar
    Alex_Berlin
    Advocate I
    8 years ago

    Hi Imke,

     

    thank you very much! This works!

     

    :smileyhappy:

     

    Alex

    • ImkeF's avatar
      ImkeF
      Community Champion
      8 years ago

      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.