Column Totals not working even with HASONEVALUE

DAX DAX Calculations
1

I need to understand why my measures in columns 2 and 4 are not totaling correctly. Here is my table.

capture1

Here are the measures for each column:

COLUMN 1:

Total Credit Adjustment =
       IF(MAX(PBI_FSCAPF[PADFMO]) =3 ||MAX(PBI_FSCAPF[PADFMO]) =6 || MAX(PBI_FSCAPF[PADFMO]) =9  ||MAX(PBI_FSCAPF[PADFMO]) =12 ,-375000,-300000)

COLUMN 2:

Credit Adjustment Total =
    VAR CREDIT_ADJUSTMENT = [Total Credit Adjustment]
    VAR TOTAL_ADJUSTED_CREDIT = SUMMARIZE(PBI_FSCAPF,PBI_FSCAPF[YEAR-MO], "Monthly Adj", CREDIT_ADJUSTMENT )
RETURN
    IF( HASONEVALUE('PBI_FSCAPF'[YEAR-MO] ),
        CREDIT_ADJUSTMENT ,
            SUMX( TOTAL_ADJUSTED_CREDIT, [Monthly Adj] ) )

COLUMN 3:

Credit Adjusment Less Inv Credits = [Total Credit Adjustment]-[Total Inv Credits]

COLUMN 4:

Credit Adjustment Less Inv Credits Total =
    VAR CREDIT_ADJUSTMENT_INV_CREDITS = [Credit Adjusment Less Inv Credits]
    VAR TOTAL_ADJUSTED_AND_INV_CREDITS = SUMMARIZE(PBI_FSCAPF,PBI_FSCAPF[YEAR-MO], "Adjustment by Month",  CREDIT_ADJUSTMENT_INV_CREDITS )
RETURN
    IF( HASONEVALUE('PBI_FSCAPF'[YEAR-MO] ),
        CREDIT_ADJUSTMENT_INV_CREDITS ,
            SUMX( TOTAL_ADJUSTED_AND_INV_CREDITS, [Adjustment by Month] ) )

I would think that these measures would work, but I’m getting totals that don’t make any sense.

Any help would be greatly appreciated!

Note: PBI_FSCAPF is my DATE table.

2

This is quite common and has everything to do with the context within the totals.

I see you’re already using good technique here to solve these so will have to look deeper at this.

Will come back with a better answer shortly. Thanks

3

Ok had another good look and realised the problem.

It’s actually to do with the initial measure you have here

Total Credit Adjustment =
       IF(MAX(PBI_FSCAPF[PADFMO]) =3 ||MAX(PBI_FSCAPF[PADFMO]) =6 || MAX(PBI_FSCAPF[PADFMO]) =9  ||MAX(PBI_FSCAPF[PADFMO]) =12 ,-375000,-300000)

This becomes an issue in the total, because the MAX results is always calculating to 12 and therefore 375,000

375,000 * 3 = 1,125,000 (which is the current total)

So it’s all to do with context here.

Because there is none in the total, every MAX( ) calc being done is look at the whole column inserted and finding the max value in the page context. So therefore 12.

Instead of using this formula initially what you’ll need to do is use an iterating function instead.

SUMX( VALUES( YEAR-MO ),
    IF( IF(MAX(PBI_FSCAPF[PADFMO]) =3 ||MAX(PBI_FSCAPF[PADFMO]) =6 || MAX(PBI_FSCAPF[PADFMO]) =9  ||MAX(PBI_FSCAPF[PADFMO]) =12 ,-375000,-300000) )

I believe this should do it.

See if you can work the same logic into your other calcs. It will be very similar.

Let me know.

Thanks

4

Sam,
I tried your suggestion and unfortunately, it did not work. I got the same results (1,125,000). However, something you said got me to thinking. You initially said that the MAX() measure was forcing it to calculate to 12, hence the 1,125,000. So on a whim, I removed the MAX() from the Total Credit Adjustment measure and that corrected my totals! Further, I did not have to create another measure using HASONEVALUE(). Here is my measure (which now works):

Total Credit Adjustment = SUMX( VALUES( PBI_FSCAPF[PADFMO] ),IF(PBI_FSCAPF[PADFMO] =3 || PBI_FSCAPF[PADFMO] = 6 || PBI_FSCAPF[PADFMO] =9 || PBI_FSCAPF[PADFMO] =12 ,-375000,-300000))

This change makes the other “branched” measure work:

Credit Adjusment Less Inv Credits = [Total Credit Adjustment]-[Total Inv Credits]

capture2

Thanks for your assistance!

Rose

5

Ah yes, sorry my bad, tried to answer to quick. But that’s exactly the adjustment you needed. Nice one.

The key when using iterating functions is that they act like a calculated column, but it all happens inside the measure at every single row.

That’s the key thing to remember on these and help out with totals a lot.

Sam