summarize not considering blank rows

Question

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&nbsp;&nbsp;M_Rev_DisplayAvg =var Vals = CALCULATETABLE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ADDCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SUMMARIZE(Sales,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SalesHier[Territory Name],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DateTable[Year-Mon],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Prod Mapping - MaterialOrSegment'[MBR Financials]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Rev", if([M_TotalRevPlanRate]=0 || ISBLANK([M_TotalRevPlanRate]), 0, [M_TotalRevPlanRate])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ALLSELECTED ())&nbsp;VAR AvgValue = AVERAGEX ( Vals, [Rev] )Return AvgValue&nbsp;&nbsp;Thank you so much in advance!NM

az38 · Answer

Hi&nbsp;nanma94&nbsp;
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&nbsp;https://docs.microsoft.com/en-us/dax/summarize-function-dax
it has no "name" argument

nanma94 · Answer

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

az38 · Answer

nanma94&nbsp;
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 · Answer

az38&nbsp; because I have a whole set of external slicers, so using calcualtetable, I am adding&nbsp;&nbsp;&nbsp;ALLSELECTED ()) to keep the extenal filters. would this make sense?&nbsp;[M_TotalRevPlanRate] is an existing measure to sum up sales revenue.&nbsp;&nbsp;

v-lionel-msft · Answer

Hi&nbsp;nanma94&nbsp;,
&nbsp;
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,&nbsp;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.
&nbsp;
Would you mind sharing your .pbix file?
&nbsp;
Best regards,Lionel Chen
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;
&nbsp;

Forum Discussion

summarize not considering blank rows

6 Replies

Related content

Parameter in summarize

SWITCH function considering zeros and blanks

Don't Summarize....still summarizes...?

Moving Average 3 Months Value - Only Considering Not Blank

Summarize dates without blanks