Problem With SUMX With IF

[HarsimranjeetSingh]

I’m trying to show value for Sales Where there’s no row for a date as zero.
Question

1. Why below non commented expression (Code1) is not showing values for missing rows in Sales Table.

2. In case i need not to use variable or doing IF on predefined measure (Code2), what is the alternate code to resolve same.

Code1

Total Sales If No Blank =
/*
VAR TotalSales = SUMX(Sales_Data,Sales_Data[Total Revenue])

RETURN

IF(ISBLANK(TotalSales),0,TotalSales)
*/
SUMX(Sales_Data,IF(ISBLANK(Sales_Data[Total Revenue])=TRUE(),0,Sales_Data[Total Revenue]))

Code2

If Sales Is Blank = IF(ISBLANK([Total Sales]),0,[Total Sales])
// Where [Total Sales] is predefined measure with expression SUM(Sales_Data[Total Revenue])

I’m confused because i came to below points for iterator w.r.t to below expression

SUMX(Sales_Data,IF(Sales_Data[Channel]=“Export”,Sales_Data[Total Revenue],0))

1. First the Evaluation Context which will be lets say first date in my visual
2. Then ,Sales table will filter for that date using active relationship.
3. Then, Iterator will work on selected rows from step 2 row by row

Thanks,
Harry

[JarrettM]

More than likely you will want to use CALCULATE for you measure, and the FILTER function to not show Sales that are blank. Please review the following tutorials from Sam regarding this to help solve your issue.

Mastering DAX Calculations

Look for this section in the tutorials:

Thanks

[HarsimranjeetSingh]

Hi @JarrettM,

Can you please provide some illustration. I’ve been through that course but not able to associate to this question, as there’s no need to change evaluation context.

Thanks,
Harry

[uriah1977]

Hi HarsimranjeetSingh,

Perhaps you can try to add +0 to your measure like:

TotalSales = SUMX(Sales_Data,Sales_Data[Total Revenue])+0

Daniel

[JarrettM]

In the future please post a sample file to help us better assist you. Your measure may be correct, but there may be another issue in your model. We don’t know unless we have access to it. Try a measure like this:

If Sales Is Blank =
SWITCH (
    TRUE (),
    [Total Sales] = BLANK (), 0,
    [Total Sales] > 0, [Total Sales],
    [Total Sales]
)

Thanks