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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Roseventura
Responsive Resident
Responsive Resident

Column Totals are not Correct even when using HASONEVALUE()

I need to understand why my measures in columns 2 and 4 are not totalling correctly. 

 

Here is my table.

capture1.JPG

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] ) )

 

Note:  PBI_FSCAPF is my DATE table.

1 ACCEPTED SOLUTION

I discovered that the MAX function in my original TOTAL CREDIT ADJUSTMENT measure was the culprit.  Here's the original measure:

 

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 was an issue in the total, because the MAX results is always calculating to 12 and therefore 375,000 * 3 = 1,125,000 (which was the incorrect total I was getting).  As soon as I removed MAX() it worked and I didn't have to use a HASONEVALUE branch measure.  This corrected measure gave me the results I wanted:

 

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

 

 

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I discovered that the MAX function in my original TOTAL CREDIT ADJUSTMENT measure was the culprit.  Here's the original measure:

 

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 was an issue in the total, because the MAX results is always calculating to 12 and therefore 375,000 * 3 = 1,125,000 (which was the incorrect total I was getting).  As soon as I removed MAX() it worked and I didn't have to use a HASONEVALUE branch measure.  This corrected measure gave me the results I wanted:

 

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

 

 

Anonymous
Not applicable

Does you Date table go back further than 2018-10?  Totals and Subtotals are calculated without any filters and each cell is caclulated independantly.  

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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