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

Using IF statement not resulting in correctly summed data

Using the following formula to combine two measures:

 

NS Act+Proj = if([NS Act Total]=0,[NS Proj Total],[NS Act Total])

 

What I want it to do is create a column where "When [NS Act Total] is zero, use values from [NS Proj Total], otherwise use [NS Act Total].

 

The above logic is working when I create a table, but the summation isn't working... summation over the year 2017 is just giving me same result as summing just [NS Act Total]

 

What am I doing wrong? See below for example. 

Adding these columnsAdding these columns... leads to just NS Act Total getting added... leads to just NS Act Total getting added

7 REPLIES 7
GabrielSantos
Resolver I
Resolver I

I might be able to help if you provide some detail on your measures and table relationships.

 

At first glance, it looks like a filter context issue. When you say IF NS Act Total = 0, then return NS Proj Total, otherwise NS Act Total, and you remove the filter context at the year summary level (the .383 value), you are in essence removing the filter. That's why I'm speculating that it could be a filter context issue.

Sounds like on right track, b/c I've noticed that depending on the data range I use to review table, the summed value changes e.g. if I just look at Sept-Dec values, it just sums those. 

 

The way the data goes together: 

[NS Act Total] = sum([NS Act]) where [NS Act] is a column of sales data for a variety of countries, by month. 

[NS Proj Total] is created same way from another column. 

 

There's a separate date table that's associated with the fact table. 

 

If using the IF statement in this way is the problem, interested in a solution that would combine these two measures. 

JC3
Frequent Visitor

Bump

Just to clarify, you are happy with the measure as it exists on the table, but when it is summarized at the grand total level, you want it to sum the two measures together?

Right. I'm using the IF statement to create a measure of the full year's activity for 2017... combine Actual where I have it, and Projected where I don't, to give me the entire 2017. 

 

Critical to that end is the ability to use it like any other measure... sum, divide, and add to other metrics. But the summation isn't working, and neither are other operations working with it. Key issue that I've run into is that, asked to sum over Jan 2017 - Dec 2017, it only adds up the [NS Act Total], not the whole thing. 

Could you share your .pbix file? 

JC3
Frequent Visitor

Bump

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.