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

If Statement Total does not add up

Hi,

I know there have been lots of questions about this topic already but I just can't figure out how to apply the solutions to my own problem.

 

I have this IF statement here:

 

 

 

 

calc = 
IF(
        MAX(DateTable[Exp_Dates]) >= MAX(A[Closed_Date])
        , [USD_AP] - [USD_FC]
        , [USD_AP]
    )

 

 

 

 

 

When now building a table I get this:

 

Screenshot 2024-01-26 132113.png

 

But what I want is the column sum of the calculated values displayed, so 155MM + .591MM. How do I do that?

 

Thanks!

1 ACCEPTED SOLUTION

@lionpbiz,

 

Here's an example you can adapt to your model. It evaluates the IF statement on a yearly basis. If you need it to evaluate at the day level, change "Year" to "Date".

 

calc = 
SUMX (
    VALUES ( DateTable[Year] ),
    IF ( DateTable[Year] >= 2024, [USD_AP] - [USD_FC], [USD_AP] )
)

 

Replace the hardcoded "2024" with the correct expression. I'm not sure what your table "A" is and how it's related to your model. The measure USD_AP has an incorrect total in your screenshot.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
DataInsights
Super User
Super User

@lionpbiz,

 

Try this measure. It iterates DateTable, so totals will be the sum of parts. Not sure if you need MAX for A[Closed_Date] since this may depend on your business logic.

 

calc =
SUMX (
    DateTable,
    IF ( DateTable[Exp_Dates] >= A[Closed_Date], [USD_AP] - [USD_FC], [USD_AP] )
)

 

Excellent article on totals:

 

https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/ 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks for the suggestion, but unfortunately this provides completely different results for me altogether.

@lionpbiz,

 

Are you able to provide sample data (table format or pbix link), screenshots of your model, and business logic?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




So I have two measures which work fine: USD_AP and USD_FC

 

Then I have my condition where basically when 2023, then use USD_AP and when 2024 then use USD_AP-USD_FC.

 

But for some reason once I do this IF statement the sum gets completely messed up eventhough the individual underlying measures work perfectly.

 

Screenshot 2024-01-26 212109.png

@lionpbiz,

 

Here's an example you can adapt to your model. It evaluates the IF statement on a yearly basis. If you need it to evaluate at the day level, change "Year" to "Date".

 

calc = 
SUMX (
    VALUES ( DateTable[Year] ),
    IF ( DateTable[Year] >= 2024, [USD_AP] - [USD_FC], [USD_AP] )
)

 

Replace the hardcoded "2024" with the correct expression. I'm not sure what your table "A" is and how it's related to your model. The measure USD_AP has an incorrect total in your screenshot.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.