cancel
Showing results 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

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:

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
Super User

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.

Proud to be a Super User!

5 REPLIES 5
Super User

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:

Proud to be a Super User!

Frequent Visitor

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

Super User

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

Proud to be a Super User!

Frequent Visitor

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.

Super User

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.

Proud to be a Super User!