Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Using IF and Calculate with Filters

I want to calculate a margin measure in terms of revenue for doing so i created grouped by tables for the level of detail of the project. however i want to be able to calculate this at business unit level ([NEGOCIO] as well hence the use of AllExcept(). Yet however when i want to use IF to determine if this margin is for business unit or supporting units for which i created a dummy measure from a column [Z2]. It appears the following error:

 

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

Below my code:

IF( [Z2] = 1, [Real/ProyAct] / calculate(SUM(BM_MargenesUPP[UPP-Ing-RealProyAct]),allexcept(BM_MargenesUPP,BM_MargenesUPP[NEGOCIO] ),IF([Z2] = 2,[Real/ProyAct] / CALCULATE(SUM(BM_MargenesGGBU[GGBU-Ing-RealProyAct])),[Real/ProyAct]/calculate(sum('BM_MargenesSG&A'[SG&A-Ing-RealProyAct])))))
 
Thanks in advance!
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous 

Try like

IF( [Z2] = 1, [Real/ProyAct] / calculate(SUM(BM_MargenesUPP[UPP-Ing-RealProyAct]),allexcept(BM_MargenesUPP,BM_MargenesUPP[NEGOCIO] )),

IF([Z2] = 2,[Real/ProyAct] / CALCULATE(SUM(BM_MargenesGGBU[GGBU-Ing-RealProyAct])),[Real/ProyAct]/calculate(sum('BM_MargenesSG&A'[SG&A-Ing-RealProyAct]))
))

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous 

Try like

IF( [Z2] = 1, [Real/ProyAct] / calculate(SUM(BM_MargenesUPP[UPP-Ing-RealProyAct]),allexcept(BM_MargenesUPP,BM_MargenesUPP[NEGOCIO] )),

IF([Z2] = 2,[Real/ProyAct] / CALCULATE(SUM(BM_MargenesGGBU[GGBU-Ing-RealProyAct])),[Real/ProyAct]/calculate(sum('BM_MargenesSG&A'[SG&A-Ing-RealProyAct]))
))

 

Anonymous
Not applicable

Thank you so much, i have another problem. When i do this and i see the margins it calculates the margin in terms of the whole company. For example the margin of the business unit is 50% but it appears 15% as is the margin it gets for the entire company.

When i use the true value of the first IF i get the correct values. Any thoughts of what could been happening?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors