Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
hrafnkel11
Helper I
Helper I

Matrix totals not correct with conditional statement

Hi – really hoping someone can help me out here. I’m encountering an issue with matrix totals not being correct and my typical HASONEFILTER fix doesn’t seem to be working (I’m probably just not applying it correctly). I’m desperate to get this fixed, as it’s affecting timelines of other projects.

 

This is the measure:

Contact_Count_Year_Start = 
IF(
    SUM('CONTACT'[CONTACT_COUNT])>0,
        CALCULATE(
            SUM('CONTACT'[CONTACT_COUNT]),
            STARTOFYEAR(DIM_DATE[DATE]
        ),BLANK()
)

 

hrafnkel11_0-1686140681282.png

 

I effectively only want to display a contact count for the beginning of the year if the sum of my contact counts are blank in the matrix. The total in this case is ignoring the IF statement in my measure and returning 4 as if it wasn’t there at all (returning 9 total). Any ideas how to fix this? Thanks!

 

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

You have to do this

IF(
    SUM('CONTACT'[CONTACT_COUNT])>0,

for each division.  Use SUMX and/or SUMMARIZE.

 

Think like the Grand Total. Very often measures designed for the Grand Total will also work for the Row and Column Totals and for the individual cells.

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

You have to do this

IF(
    SUM('CONTACT'[CONTACT_COUNT])>0,

for each division.  Use SUMX and/or SUMMARIZE.

 

Think like the Grand Total. Very often measures designed for the Grand Total will also work for the Row and Column Totals and for the individual cells.

Thanks! I solved it using the below (prefixing the code with a SUMX(VALUES(...)). Does this work because I'm basically saying "perform this calculation for every instance of ACCOUNT_NAME, then iterate through all account names"? 

 

Contact_Count_Year_Start = 
SUMX(
    VALUES('CONTACT'[ACCOUNT_NAME]),
IF(
    SUM('CONTACT'[CONTACT_COUNT])>0,
        CALCULATE(
            SUM('CONTACT'[CONTACT_COUNT]),
            STARTOFYEAR(DIM_DATE[DATE]
        ),BLANK()
))

yes.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.