cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## 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()
)``````

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
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.

3 REPLIES 3
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.

Frequent Visitor

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()
))``````
Super User

yes.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors