calculate averages with null values?

[elenio]

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

[AlexisOlson]

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.

[Whitewater100]

Hi:

Perhaps one alternative:

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

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

[elenio]

this just returns null 😞

[Whitewater100]

Hello:

Did you look at the file I shared last time?

On Page 1 the results are:

[elenio]

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

[elenio]

Hi Thanks,

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

THANKS!

[Whitewater100]

You can add this measure to the file posted:

Avg Amt Filtered =

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

[Whitewater100 · Accepted solution]

Hi:

Look at my page 1. It works here. Thanks

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

[ValtteriN]

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/

[elenio]

hi there,

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

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

thx