topic Re: calculate averages with null values? in DAX Commands and Tips

calculate averages with null values?

elenio — Fri, 25 Feb 2022 11:14:45 GMT

Hi there,

i want to analyze my shops average cart combination.

therefore i have clustered my products in main categories and put them in a matrix. e.g.:

in order 1 someone bought 1 x 4kg product and

in order 2 someone bought 6x Probe.

so far so good.

In the sum line instead of

1 ; 6 ; 7

i would like to see

0,5; 3; 3,5

because it is the average bought.

when i create a DAX now:

AVG = DIVIDE(SUM('Order'[Qty]), DISTINCTCOUNT('Order'[OrderNbr]))

i get this:

the totals average is fine, but the individual columns not.

i guess when QTY = null the DISTINCTCOUNT does not work. ...

any idea?

thanks

Re: calculate averages with null values?

ValtteriN — Fri, 25 Feb 2022 11:41:05 GMT

Hi,

Try something like this:

Measure 19 =

IF(isblank(SELECTEDVALUE('Table (7)'[Column3])),

divide(sum('Table (7)'[Column2]),COUNTROWS(DISTINCT(all('Table (7)'[Column3]))))

,sum('Table (7)'[Column2]))

Here I use SELECTEDVALUE to indentify total row and use a separete logic there. For the rest of the rows I simply use the "order value".

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: calculate averages with null values?

elenio — Fri, 25 Feb 2022 12:28:25 GMT

hi there,

looking at your Column A sum, your solution is not correct:

(100+300)/7 = 57,1 and not 50

thx

Re: calculate averages with null values?

Whitewater100 — Fri, 25 Feb 2022 14:43:12 GMT

Hi:

Perhaps one alternative:

Avg Amt = IF(NOT(ISFILTERED(Order[Order#])),

DIVIDE(SUM(Order[Qty]), COUNTROWS(ALL(Order))))

Re: calculate averages with null values?

elenio — Fri, 25 Feb 2022 19:06:51 GMT

this just returns null 😞

Re: calculate averages with null values?

Whitewater100 — Fri, 25 Feb 2022 20:20:51 GMT

Hi:

Look at my page 1. It works here. Thanks

https://drive.google.com/file/d/1gElEdPCYwG6Xv_pc_xlWGkdKLC3dsrY5/view?usp=sharing

Re: calculate averages with null values?

AlexisOlson — Fri, 25 Feb 2022 22:05:26 GMT

The "averages" you want don't really make sense as averages, IMO. There's only one 4kg order, so the average is simply 1.

If you really do want to average over non-existent orders too, then I strongly recommend creating a dimension table for your orders so you don't have to fight against auto-exists. With a dimension table, it becomes much easier to write formulas for combinations that don't necessarily exist in your fact table.

Re: calculate averages with null values?

Whitewater100 — Sun, 27 Feb 2022 19:03:54 GMT

Hello:

Did you look at the file I shared last time?

On Page 1 the results are:

Re: calculate averages with null values?

elenio — Mon, 28 Feb 2022 09:27:08 GMT

Hi Thanks,

still trying to figure out, why it is not working with my data. but yours seems to work fine 😉

THANKS!

Re: calculate averages with null values?

elenio — Mon, 28 Feb 2022 09:33:52 GMT

ok. i had a filter on a set of order# to check the solution. if i filter in your example, it also returns nulls.

Re: calculate averages with null values?

Whitewater100 — Mon, 28 Feb 2022 15:53:34 GMT

You can add this measure to the file posted:

Avg Amt Filtered =

DIVIDE(SUM(AvgTable[Qty]), COUNTROWS(ALL(AvgTable)))