Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
7 months ago
Solved

Column Grand Total in Matrix is wrong

Hi, The Column Grand Total in my Matrix is showing just the current January total and ignoring everything before it - it was working just fine before 1st January:   The Matrix is compo...
Need Help
Show and Tell
  • GrowthNatives's avatar
    GrowthNatives
    7 months ago

    Hi ArchStanton , you can try these steps to get the desired result

    You need two different logics:

    One for monthly cells

    One for totals

    Replace your measure with this :

    DAX 
YTD New Cases :=
IF (
    ISINSCOPE ( 'Date'[Month] ),
    -- Row level (month)
    TOTALYTD (
        COUNT ( 'Cases'[Case Number] ),
        'Cases'[Created On]
    ),
    -- Total level
    SUMX (
        VALUES ( 'Date'[Month] ),
        TOTALYTD (
            COUNT ( 'Cases'[Case Number] ),
            'Cases'[Created On]
        )
    )
)

    What this does

    When a Month is in scope → normal YTD logic

    When Month is NOT in scope (Grand Total):

    Iterates each visible month

    Calculates YTD per month

    Sums those values

    Alternative , If you have a proper Date dimension

    DAX 
YTD New Cases :=
CALCULATE (
    COUNT ( 'Cases'[Case Number] ),
    DATESYTD ( 'Date'[Date] )
)

    And apply the same ISINSCOPE total fix if needed.

    ⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
    💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
    🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]

GrowthNatives's avatar
GrowthNatives
Super User
7 months ago

Hi ArchStanton , you can try these steps to get the desired result

You need two different logics:

One for monthly cells

One for totals

Replace your measure with this :

DAX 
YTD New Cases :=
IF (
    ISINSCOPE ( 'Date'[Month] ),
    -- Row level (month)
    TOTALYTD (
        COUNT ( 'Cases'[Case Number] ),
        'Cases'[Created On]
    ),
    -- Total level
    SUMX (
        VALUES ( 'Date'[Month] ),
        TOTALYTD (
            COUNT ( 'Cases'[Case Number] ),
            'Cases'[Created On]
        )
    )
)

What this does

When a Month is in scope → normal YTD logic

When Month is NOT in scope (Grand Total):

Iterates each visible month

Calculates YTD per month

Sums those values

Alternative , If you have a proper Date dimension

DAX 
YTD New Cases :=
CALCULATE (
    COUNT ( 'Cases'[Case Number] ),
    DATESYTD ( 'Date'[Date] )
)

And apply the same ISINSCOPE total fix if needed.

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]

  • ArchStanton's avatar
    ArchStanton
    Power Participant
    7 months ago

    Thank you so much for this - I applied your 1st solution with ISINSCOPE and it works perfectly.

    I tried your 2nd suggestion so I duplicated the report page but I couldn't get it to work.

    New YTD DAX = 
    CALCULATE(
        COUNT('Cases'[Case Number]),
            DATESYTD(Date2[Date]))

     

    Before I accept your 1st one as a solution - can you explain what maybe happening here please?
    Thanks!

    • GrowthNatives's avatar
      GrowthNatives
      Super User
      7 months ago

      ArchStanton , sure. I can do that for you


      Key facts about TOTALYTD

      1. TOTALYTD resets at the start of each year

      2. It depends entirely on the current filter context

      3. Grand Totals do NOT iterate months — they evaluate the measure once

       

      What changed on January 1st (critical insight)

      Before Jan 1

      • All visible months were in the same calendar year

      • Grand Total context = “latest date in year”

      • YTD up to Dec = full year

      • Result looked correct

      After Jan 1

      • Your Matrix now contains:

        • Months from previous year(s)

        • AND January of the new year

      When Power BI evaluates the Column Grand Total:

      • There is no Month filter

      • Only a Date filter

      • The latest date in context is January

      • TOTALYTD sees:
        “I’m in January → YTD = January only 

      Why this only affects the column grand total

       

      • Rows = Month → evaluated month by month

      • Columns = Team → fine

      • Column Grand Total = evaluated once, not per row

       

       

      • ArchStanton's avatar
        ArchStanton
        Power Participant
        7 months ago

        Much appreciated!!