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 composed of the following 3 elements:

 

YTD New Cases = 
TOTALYTD ( COUNT ( 'Cases'[Case Number] ),
'Cases'[Created On], "01/01/2016")

 

This has never happened in previous years, any ideas what maybe causing this?
Thanks

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]

8 Replies

  • 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

         

         

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    7 months ago

    TOTALYTD() returns one value for the filter context it’s evaluated in. It takes the max date in that context and returns the YTD up to that date. In the month columns, the context is “this month”, so YTD is fine per month. Instead that use the formula below:

     

    YTD New Cases =
IF (
    ISINSCOPE ( 'Date'[Month] ),
    TOTALYTD ( COUNT('Cases'[Case Number]), 'Date'[Date] ),
    SUMX (
        VALUES ( 'Date'[Month] ),
        CALCULATE( TOTALYTD ( COUNT('Cases'[Case Number]), 'Date'[Date] ) )
    )
)