Forum Discussion

elenio's avatar
elenio
Frequent Visitor
4 years ago
Solved

calculate averages with null values?

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

10 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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's avatar
      elenio
      Frequent Visitor
      4 years ago

      hi there,

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

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

       

       

      thx

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    Perhaps one alternative:

    Avg Amt = IF(NOT(ISFILTERED(Order[Order#])),
    DIVIDE(SUM(Order[Qty]), COUNTROWS(ALL(Order))))
  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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.