Forum Discussion

hrafnkel11's avatar
hrafnkel11
Helper I
3 years ago
Solved

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 apply...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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.

lbendlin's avatar
lbendlin
Super User
3 years ago

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.

hrafnkel11's avatar
hrafnkel11
Helper I
3 years ago

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()
))
  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    yes.