Value missing from Matrix table after using SUMX

Hi cj_oat ,

The issue you’re facing is due to how SUMX works within the row context. The problem arises because _QualifiedFlag is being evaluated in the wrong context when you use SUMX. To resolve this and get the correct results for both individual cells and the totals, you need to make sure the measure respects the context correctly.

Here’s the corrected measure:

Qualified Flag Count = 
SUMX(
    'Table',
    VAR _ActiveFlag = IF([ActiveFlag] > 0, 1, BLANK())
    VAR _SalesInv = IF([Cumulative Sales] + [Inventory] > 0, 1, BLANK())
    VAR _QualifiedFlag = IF(_ActiveFlag + _SalesInv = 2, 1, BLANK())
    RETURN _QualifiedFlag
)

Key Adjustments:

1. Moving Calculations Inside SUMX:

   • _ActiveFlag, _SalesInv, and _QualifiedFlag are now evaluated per row in the table context within SUMX.

2. Ensuring Proper Context:

   • By defining the logic inside the SUMX, the calculation works for each row of the table correctly, and the results can be aggregated as expected.

3. Check Table Rows:

   • Ensure that 'Table' contains all relevant rows for the months (e.g., ensure rows for June and July exist in the table, even if some fields are blank).

Debugging Tips:

• Use a calculated column to debug _ActiveFlag and _SalesInv if needed to ensure these intermediate values are being computed correctly for the months in question.
• Test _QualifiedFlag independently to confirm it produces the expected results for each row.

Why Your Initial Measure Failed:

• In the first version of your measure, _QualifiedFlag was returning BLANK() for certain months because the SUMX function was iterating over the table but _QualifiedFlag was evaluated outside of the row context.

Let me know if this works!

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

Hi FarhanJeelani ,

Thanks for the advices! I just checked and realized that in June & July, the rows of data does not exist in the source table. Is there any way to fix this?

I used to create table to combine all combination of SKU and month and other data but then it collapsed because the data is too big...

Hi ,

Based on the description, try to create a table that includes the dates you need. Try using the following DAX formula.

Qualified Flag Count =
VAR _ActiveFlag = IF([ActiveFlag] > 0, 1, 0)
VAR _SalesInv = IF([Cumulative Sales] + [Inventory] > 0, 1, 0)
VAR _QualifiedFlag = IF(_ActiveFlag + _SalesInv = 2, 1, 0)
RETURN
SUMX(
    CALENDAR(MIN('Table'[Date]), MAX('Table'[Date])),
    _QualifiedFlag
)

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.