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]

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