Forum Discussion
Calculating average including blanks
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
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.
6 Replies
Try adding 0 to your measure.
Ex:
Measure = Calculate(SUM(Table[Column]),FILTER())+0
Then use that SUM measure result in your Average calculation.
This is my formula currently
Invoiced Cases Average =
AVERAGEX(VALUES(DimDate[Week in Year] ), [Invoiced Cases] )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).
