summarize not considering blank rows

[nanma94]

I have a dax that calculate the average sales of selected sales team - summarize will not return a record for territories that have no revenue, though in below its checking 0 and null. I need these 0 value territories to be included otherwise the average is inflated. In the data model, "sales" is the fact table that glues all the dimension tables. So 

M_Rev_DisplayAvg =

var Vals = CALCULATETABLE(
                     ADDCOLUMNS (
                         SUMMARIZE(Sales,
                                              SalesHier[Territory Name],
                                              DateTable[Year-Mon],
                                              'Prod Mapping - MaterialOrSegment'[MBR Financials]),
                    "Rev", if([M_TotalRevPlanRate]=0 || ISBLANK([M_TotalRevPlanRate]), 0, [M_TotalRevPlanRate])),
                 ALLSELECTED ())

VAR AvgValue = AVERAGEX ( Vals, [Rev] )

Return AvgValue

Thank you so much in advance!
NM

[v-lionel-msft]

Hi @nanma94 ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @nanma94 

I don't like ypur summarize statement

SUMMARIZE(Sales,
                                              SalesHier[Territory Name],
                                              DateTable[Year-Mon],
                                              'Prod Mapping - MaterialOrSegment'[MBR Financials])

It should have a little bit other syntax https://docs.microsoft.com/en-us/dax/summarize-function-dax

it has no "name" argument

[nanma94]

@az38Thank you! I will add the names. But will the names fix the issue... 🙂

[az38]

@nanma94 

as I understand its name "M_TotalRevPlanRate"?

why do you need calculatetable?

M_Rev_DisplayAvg =

var Vals = 
                     ADDCOLUMNS (
                         SUMMARIZE(Sales,
                                              SalesHier[Territory Name],
                                              DateTable[Year-Mon],
                                              "M_TotalRevPlanRate",
                                              'Prod Mapping - MaterialOrSegment'[MBR Financials]),
                    "Rev", if(ISBLANK([M_TotalRevPlanRate]), 0, [M_TotalRevPlanRate]))
                
VAR AvgValue = AVERAGEX ( Vals, [Rev] )

Return AvgValue

works pretty good for my dummy data

[nanma94]

@az38  because I have a whole set of external slicers, so using calcualtetable, I am adding   ALLSELECTED ()) to keep the extenal filters. would this make sense?

[M_TotalRevPlanRate] is an existing measure to sum up sales revenue. 

[v-lionel-msft]

Hi @nanma94 ,

The reason for the error is the following step.

 if([M_TotalRevPlanRate]=0 || ISBLANK([M_TotalRevPlanRate]), 0, [M_TotalRevPlanRate])

Since the [M_TotalRevPlanRate] is a measure, it's very easy to be wrong to use conditional judgment statements and summaries on it.

The easiest way is to change this measure to a calculated column.

Would you mind sharing your .pbix file?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.