Averagex not working when calculating 'days with sales'

[emilmorkeberg]

Hi!

Im trying to calculate an average measure per SKU (called "Productivity"). However, when I'm not selecting any SKU, the Averagex-measure leaves me with the "NaN" error. I really hope someone can help me, as I struggle to find a proper solution.

Average Productivity per SKU =

AVERAGEX(

    KEEPFILTERS(VALUES('Item'[Item Number Name])),

    CALCULATE([Productivity per SKU])

)

The measure above works perfectly fine when I highlight/select a specific SKU from a visual. However, when I don't select a SKU I receive the 'NaN' error. I have found out the reason is the '# Days w Sales' in the 'Productivity per SKU'-measure, as I can replace it with a random number and all the calculations seem to work fine with no errors.

Productivity per SKU =

[Sales] / [# Days w Sales] / [# Stores w Sales]

I use the following measure to calculate the numbers of days with sales. I have also tried others like DISTINCTCOUNT and likely:

# Days w Sales =

CALCULATE(COUNTROWS('Date'),FILTER(Sales,[Sales] > 0))

So TLDR;

Do you have any alternative ways to calculate # Days w Sales that will work with my Averagex calculation Average Productivity per SKU? Please let me know if I need to explain further or if you need any other info in order to solve the problem.

Thank you in advance!

[emilmorkeberg · Accepted solution]

Turns out there were missing days/stores for some items in the calculation and thus the error.

IFERROR solved the problem fine.

[v-eqin-msft]

Hi @emilmorkeberg ,

Typically you get NaN when you try to calcualte 0/0

So we recommend that you use the DIVIDE function whenever the denominator is an expression that could return zero or BLANK.

For your case, i think the DAX you used for [# Days w Sales] is fine, just try to  use DIVIDE() instead of /  

=DIVIDE( DIVIDE([Sales] / [# Days w Sales]), [# Stores w Sales] )

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

[amitchandak]

@emilmorkeberg , if sales is measure , prefer

CALCULATE(COUNTROWS('Date'),FILTER(values('Date'[Date]),[Sales] > 0))

Also use the divide function

example 

 divide(divide([Sales], [# Days w Sales]),[# Stores w Sales])