Switch formula doesn't work correctly

[MarieD]

Hi all,

I'm currently working on a financial report with a template. I want to make a difference in amount in euro's and the percentages.

For both I use a switch formula which is working as expected for the amounts, but the same formula used for the percentages, i only returning values for part of the rows, and not for all the rows in between as shown in table on the left.

When not used in the template but directly to the financial descriptions, the actual% is giving correct amounts. But when I want to use it whith the template descriptions, something went wrong..

This is the formula for the amounts that works fine:

Selected Year Actuals =

Var CurrentItem = SELECTEDVALUE('Template WV'[Omschrijving-normalized])

RETURN

SWITCH ( TRUE(),

CurrentItem = "Omzet", DIVIDE([Omzet],1,0),

CurrentItem = "Bedrijfsresultaat", DIVIDE([Bedrijfsresultaat],1,0),

CurrentItem = "Operationele kosten", DIVIDE([OperationeleKosten],1,0),

CurrentItem = "Brutowinst", DIVIDE([Brutowinst],1,0),

CurrentItem = "Resultaat voor belasting", DIVIDE([Resultaat voor belasting],1,0),

CurrentItem = "Nettoresultaat", DIVIDE([Nettoresultaat],1,0),

CALCULATE( [Actuals] , FILTER( 'Financiele resultaten' , 'Financiele resultaten'[Omschrijving]= CurrentItem ) ) )

And this is the second formula for the percentages that is NOT working as I want:

Selected Year Actuals % =

Var CurrentItem = SELECTEDVALUE('Template WV'[Omschrijving-normalized])

RETURN

SWITCH ( TRUE(),

CurrentItem = "Omzet", DIVIDE([Omzet%],1,0),

CurrentItem = "Bedrijfsresultaat", DIVIDE([Bedrijfsresultaat%],1,0),

CurrentItem = "Operationele kosten", DIVIDE([OperationeleKosten%],1,0),

CurrentItem = "Brutowinst", DIVIDE([Brutowinst%],1,0),

CurrentItem = "Resultaat voor belasting", DIVIDE([ResultaatVoorBelasting%],1,0),

CurrentItem = "Nettoresultaat", DIVIDE([Nettoresultaat%],1,0),

CALCULATE( [Actuals%] , FILTER( 'Financiele resultaten' , 'Financiele resultaten'[Omschrijving]= CurrentItem ) ) )

Both are exactly the same, so I don't know what is wrong? It would be great if somebody can help me!

Many thanks,

[amitchandak]

@MarieD , formula seems ok,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[MarieD]

@amitchandak

How can i share the pbix file on this forum?

[vivran22]

@MarieD 

You may use One Drive/Google Drive/Drop box or similar options to share the file. Save the file in the shared location and post the link on this thread.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[MarieD]

Hi,

this is the link to the file:

https://www.dropbox.com/s/igzsmi89cml4yq7/Versie%20Helpforum.pbix?dl=0

It would be great if somebody could help me!

thanks!

[vivran22]

@MarieD 

The way I see it, your all calculation is coming from the table marked in red, and you are filtering it using the table marked in blue

Since there is no relationship between them, wherever you have defined the filter context in your measure, it is giving you the result, otherwise it is returning blank.

So, either we establish a relationship, then we can get the two tables to interact or we modify our DAX.

PS: I believe there is a significant scope of improving the model and the meausres. For instance, why we are dividing the meausres by 1?

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[MarieD]

Hi,

I know I still have to look into the measures, they are not completely correct yet, but I think that should not have impact on wheather the SWITCH formula is giving any results or not? For some rows it is not giving back anything at all?

I know there is not a relationship between those 2 tables, but that is also the case for the first formula, and that one is working fine. On another topic about financial reports with a template, it was told not to link the 2 tables.. So that's how I got it working for the first one, and i tried to set up the 2nd one exaxctly the same, but I can't get that one working.

Would your advise be to change the relationships then?

[vivran22]

@MarieD 

What is the logic behind creating the Tenplate WV table? Also, which finance template you are referreing to? Is there any link for that?

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Bertie]

If I were you I would repair the measures or test this problem with only the properly working measures first. You will have to do this eventually and it would help in solving this problem.

[bertalan_ronai]

Omzet% = DIVIDE([Omzet],[Omzet]) -this measure looks strange. Are you sure it is good?