topic Total Is Correct but row is not when using ALL in DAX Commands and Tips

Total Is Correct but row is not when using ALL

BrianNeedsHelp — Wed, 08 Jan 2025 01:18:10 GMT

Biggest puzzler yet: I have this measure called [%Need]. The sum total of these percentages needs to be 1 in order to compute correctly. However, it's not, so in order to correct it I need to do a formula like: [%Need]/Sum [%Need] *1. So in order to get sum portion I did:

CALCULATE(SUMX(VALUES('Calendar'[calendar date]),[%Need]),ALL('Calendar'[CalendarDate]))

This puts 1(I checked it out to 15 0s 1.00000000000000 in each row instead of the correct amount which is 1.0147. 1.0147 however appears in the Total at the bottom. I need 1.0147 in each row so I can have it divided by [%Need]. This probably doesn't need to be included, but just in case the [%Need] measure is:

IF([CALendarDATE]>LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds]),[PYGA]/([PYALLGAs]-[GAsLyMAXDateAll]),Blank())

Any idea how to get the correct total in each row?

Re: Total Is Correct but row is not when using ALL

FarhanJeelani — Wed, 08 Jan 2025 01:58:34 GMT

Hi BrianNeedsHelp,

The issue arises because DAX treats row context and filter context differently when calculating totals versus individual rows. Here's a breakdown of what's happening and how to fix it:

Problem:

The total row correctly shows 1.0147, but individual rows incorrectly show 1.0000.
This is because you're using ALL('Calendar'[CalendarDate]), which removes the filter context for the column. This causes the calculation to consider all dates, not just the rows' specific contexts.

Solution:

You want each row to reflect the correct percentage by dividing the row's %Need value by the sum total (1.0147). To achieve this:

Corrected Measure

[%Need Adjusted] =
VAR TotalNeed =
    CALCULATE(SUMX(VALUES('Calendar'[Calendar Date]), [%Need]), ALL('Calendar'))
RETURN
    DIVIDE([%Need], TotalNeed)

Why This Works:

CALCULATE with ALL:
- The ALL function removes all filters on the Calendar table, ensuring that the total sum of %Need is calculated across the entire dataset.
Row-Level Context:
- Within each row, %Need is calculated based on the current filter context.
- The TotalNeed variable ensures you're dividing each row's %Need by the same total value.
DIVIDE for Safety:
- Using DIVIDE avoids division-by-zero errors and is best practice for these scenarios.

Debugging and Validation

Add a temporary measure to verify the TotalNeed:
```
TotalNeed = CALCULATE(SUMX(VALUES('Calendar'[Calendar Date]), [%Need]), ALL('Calendar'))
```
This should return 1.0147 consistently across all rows.
Test the %Need Adjusted measure in your table visual.

Let me know if this resolves the issue or if you need further clarification!

Please mark this as solution if it helps you. Appreciate Kudos

Re: Total Is Correct but row is not when using ALL

BrianNeedsHelp — Wed, 08 Jan 2025 03:16:45 GMT

Thanks for your reply and explanation. However that measure does the same thing. 1 in all the rows and 1.0147 in the total.