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]

ArchStanton's avatar
ArchStanton
Power Participant
7 months ago

I have one last final question if thats ok?

How do I exclude January (current month) completely from the Matrix?
I've been trying all sorts of filters and IF statements using e.g.

VAR CurrentMonth = EOMONTH(TODAY(),0) etc but I cannot get it to work:
 
ISINSCOPE New Cases = 
IF(
    ISINSCOPE(Date2[Mth]), -- Row Level (month) in scope so normal YTD logic
    TOTALYTD(
        COUNT('Cases'[Case Number]),
        'Cases'[Created On]
    ),
    -- Total level (month is not in scope so each visible mth is iterated)
    SUMX(
        VALUES(Date2[Mth]),
        TOTALYTD(
            COUNT('Cases'[Case Number]),
            'Cases'[Created On]
        )
    )
)
GrowthNatives's avatar
GrowthNatives
Super User
7 months ago

1. Define a cutoff date

VAR CutoffDate =
    DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

 
2. Apply the cutoff inside CALCULATE

YTD New Cases (Exclude Current Month) =
VAR CutoffDate =
    DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

RETURN
IF(
    ISINSCOPE(Date2[Mth]),
    
    -- Month rows
    CALCULATE(
        TOTALYTD(
            COUNT('Cases'[Case Number]),
            'Cases'[Created On]
        ),
        'Cases'[Created On] < CutoffDate
    ),

    -- Matrix Total
    SUMX(
        VALUES(Date2[Mth]),
        CALCULATE(
            TOTALYTD(
                COUNT('Cases'[Case Number]),
                'Cases'[Created On]
            ),
            'Cases'[Created On] < CutoffDate
        )
    )
)

 

  • ArchStanton's avatar
    ArchStanton
    Power Participant
    7 months ago

    Many thanks for all of your help!