Financial template P&L issues

DAX DAX Calculations
1

Hi,

I’m working on a P&L overview, basesdon working with a template as per the video Financial Reporting with Power BI. Unfortunatelly, I’m facing 2 problems now and I really don’t know how to fix them.

  1. The first problem is with the sheet called P&L Years. I have calculated this year and last year values, and the difference between TY and LY. But for “Omzet” it will not give me the correct difference in the table. Whileif i calculate it manually, so with a measure “Omzetverschil” for Omzet - Omzet LY, it is correct. All other differcences seems to be okay so I don’t know what is wrong with it?

  2. The 2nd problem is on sheet called P&L Percentages. I want to calculate all amounts as a percentage of “Omzet”. So i have made calculations for all subtotals and they work fine, but all rows in between should also be filled in. If not used in a template table but in a normal table, the actuals% formula is working as you can see in the table on the right. But I can’t figure out why this is not working in the table with the template? I used the same formula as i did with the amounts in euro’s (see on page Winst and Verliesrekening) and there it is working correctly…

It would be great if somebody can help me, many thanks!

Versie met Template.pbix (1.7 MB)

1 Like
2

Hi Marieke,

Welcome to the Forum!

1 - Was caused by a “)” in the wrong place… this should sort it.

TY vs LY Actuals = 
VAR CurrentItem = SELECTEDVALUE('Template WV'[Omschrijving-normalized] )

RETURN
SWITCH( TRUE(),
    CurrentItem = "Omzet", [Omzet] - [Omzet LY],
    CurrentItem = "Operationele kosten", [OperationeleKosten] - [OperationeleKosten LY],
    CurrentItem = "Bedrijfsresultaat", [Bedrijfsresultaat] - [Bedrijfsresultaat LY],
    CurrentItem = "Brutowinst",  [Brutowinst] - [Brutowinst LY],
    CurrentItem = "Resultaat voor belasting", [Resultaat voor belasting] - [Resultaat voor belasting LY],
    CurrentItem = "Nettoresultaat", [Nettoresultaat] - [Nettoresultaat LY],
    CALCULATE( [Actuals], FILTER( 'Financiele resultaten', 'Financiele resultaten'[Omschrijving] = CurrentItem ))  - 
    CALCULATE( CALCULATE( [Actuals], FILTER( 'Financiele resultaten', 'Financiele resultaten'[Omschrijving] = CurrentItem ) ), DATEADD( Kalender[Datum], -1, YEAR ) )
)

.

2- When you replace [Actuals%] with your [Omzet] measure, like below

Selected Year Actuals % = 
Var CurrentItem = SELECTEDVALUE('Template WV'[Omschrijving-normalized])

RETURN  
SWITCH ( TRUE(),
    CurrentItem = "Omzet", [Omzet%],
    CurrentItem = "Bedrijfsresultaat", [Bedrijfsresultaat%],
    CurrentItem = "Operationele kosten", [OperationeleKosten%],
    CurrentItem = "Brutowinst", [Brutowinst%],
    CurrentItem = "Resultaat voor belasting", [ResultaatVoorBelasting%],
    CurrentItem = "Nettoresultaat", [Nettoresultaat%],
    CALCULATE( [Omzet] , FILTER( 'Financiele resultaten', 'Financiele resultaten'[Omschrijving]= CurrentItem ) ) 
    //CALCULATE( [Actuals%] , FILTER( 'Financiele resultaten', 'Financiele resultaten'[Omschrijving]= CurrentItem ) ) 
)

You get all blanks because [Omzet] is defined as ‘Financiele resultaten’[Omschrijving]=“Omzet”

I hope this is helpful.

3

Hi @Melissa

Many thanks for your help, this first one is sorted now so I’m very happy with that.

But for the second question, perhaps I was not clear in explaining what I wanted to have.
For all rows, except the subtotals, it should return the values in euro’s as a percentages of the [Omzet]. So that is what I tried to do in the measure [Actuals%].

The measure itselves seems to be working fine. For example, Personeelkosten / Omzet = 178k/670k = 25%. This should also be returned in the template table on the left.

When i replace [Actuals] by [Omzet] is is still returning me blanks?

4

Yes! you are calculating [Omzet] over another dimension in that separate table.

If you examine your [Omzet] measure it has a filter on ‘Financiele resultaten’[Omschrijving]=“Omzet”

So there is no way you can return a value for ‘Financiele resultaten’[Omschrijving]=“Omzet” AND ‘Financiele resultaten’[Omschrijving] = CurrentItem (unless that CurrentItem = Omzet)

You need to tell DAX which ‘Financiele resultaten’[Omschrijving] should be summed up for each ‘Template WV’[Omschrijving-normalized] because that is the dimension in your table.

I hope this is helpful.

5

Hi,

i have now changed the formula to this:

Selected Year Actuals % =
Var CurrentItem = SELECTEDVALUE(‘Template WV’[Omschrijving-normalized])

RETURN
SWITCH ( TRUE(),
CurrentItem = “Omzet”, [Omzet%],
CurrentItem = “Bedrijfsresultaat”, [Bedrijfsresultaat%],
CurrentItem = “Operationele kosten”, [OperationeleKosten%],
CurrentItem = “Brutowinst”, [Brutowinst%],
CurrentItem = “Resultaat voor belasting”, [ResultaatVoorBelasting%],
CurrentItem = “Nettoresultaat”, [Nettoresultaat%],

FORMAT( DIVIDE( CALCULATE( [Actuals],
FILTER( ‘Financiele resultaten’, ‘Financiele resultaten’[Omschrijving] = CurrentItem ) ), [Omzet], 0 ), “0.00%” ))

And now it is working :slight_smile:
Many thanks for your help!

1 Like