Calculating average including blanks

[jpt1228]

Hello, I have read through the other messages but can't get this figured out. I have a DimAte table with Week in Year measure and an invoiced fact table with a measure for cases and also a column for cases.

I am calcuating the weekly average but couldn't figure out why most worked and a few didn't. I finally fount out that the formula is not adding the blanks into the calculation. I need to cout all the weeks of sales, including blanks and return the average over the number of weeks sleected. In the below example this is 5 weeks (36-40). Item A average should be 51.2 but my formula is returning 256.

One other key is I filter this report on the last 10 calendar weeks and control click 1 week, 3 weeks, 5 weeks etc to filter the data in the report.

 

Thanks 

Jon

[jpt1228]

This is my formula currently

Invoiced Cases Average =
AVERAGEX(VALUES(DimDate[Week in Year] ), [Invoiced Cases] )

[Chihiro]

You'd need to do it in two stages.

1. SUM as I indicated.

2. Then you'd use SUM Measure/# of weeks (or days).

[Chihiro]

Try adding 0 to your measure.

Ex:

Measure = Calculate(SUM(Table[Column]),FILTER())+0

Then use that SUM measure result in your Average calculation.

[jpt1228]

@Chihiro I'm getting an error w/your formula

[Chihiro]

... What I gave is formula construct 😉

You will need to supply appropriate arguments.

If you need more specific help, it would help if you can upload sample model.

[jpt1228 · Accepted solution]

Ok, so what I ended up doing is creating a sales = calculate(sum(Dollar Sales)) and another one is number of weeks =distinctcount(dimdate[week in year]) and then divide (sales,number of weeks) to the the correct average then I filter on the page for number os specific weeks (Last 4 weeks, last 10 weeks etc).

Thanks for giving me the idea to create the seperate measures and then divide them.