cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarieD
Frequent Visitor

Switch formula doesn't work correctly

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..

 

vb PBI Voorbeeld.png

 

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,

 

9 REPLIES 9
amitchandak
Super User
Super User

@MarieD , formula seems ok,

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak

 

How can i share the pbix file on this forum?

 

 

vivran22
Community Champion
Community Champion

@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
Frequent Visitor

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
Community Champion
Community Champion

@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

vivran22_0-1597800914223.png

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
Frequent Visitor

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
Community Champion
Community Champion

@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
Regular Visitor

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.

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!