Average in DAX based on grouping column that takes into consideration ZERO values for count

[Anonymous]

Hello.

I want to calculate average for a single column based on months. The issue is that I have the dates in my fact table and the rows with the value 0 should be taken into consideration when calculating based on month. Therfore if I have 10 rows with 0 in January 2020, there will be counted as 10, but I want them to be counted as 1. And of course any other external filters (not coming from the Calendar Dimmension) table should be taken into consideration if applied.

Please see the below example ( the left part is what I get now, the right is what I want to get).

Since I have a lot of rows, creating a separate table for this requirement would slow things down. I would like to obtain this by using only DAX, if possible, since I am connecting to a live model and I don't have Query Editor or Custom Columns available.

You can find a sample PBI file that you can use here:

https://drive.google.com/file/d/1O20pNok50-Hb02olyZ2PtlU8A8kG_wmv/view?usp=sharing

Thank you!

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Would you please refer to the following measure by your sample:

Average of value = AVERAGEX(SUMMARIZE(Sales,'Date'[Month],Sales[Product Type],"Value",SUM(Sales[Value])),[Value])

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

Best Regards,

Dedmon Dai

[Anonymous]

Thank you very much @v-deddai1-msft . Your DAX works great. I still have to put all the columns that I want to group by inside the Summarize function, but PBI handles this very good.

[amitchandak]

@Anonymous , Try a measure like

Measure =
Var _1 = sum(Table[Value])
var _2 = countx(summarize(Table, Table[type], table[Date], "_1", calculate(sum(Table[Value]))), [_1]>0)
var _3 = if(isblank(countx(summarize(Table, Table[type], table[Date], "_1", calculate(sum(Table[Value]))), [_1]=0)),0,1)
return
divide(_1,_2+_3)

If value is a measure remove sum

[Anonymous]

@amitchandakthank you for your answer. Unfortunatelly it does not work.

It says that COUNTX can not be used with boolean value (for VAR _2). Besides I can't use your approach because you put there the column "type". I have around 15 columns coming from various tables that might change over time. I can't put all of them in the DAX formula.

However if I try to put COUNTAX instead of COUNTX i get the same values no matter what I put in the condition. I can put [_1] = -1000000 and I still get the same numbers.

Maybe you could download the sample file I posted above and give it a try.

Thank you!