Forum Discussion

jpt1228's avatar
jpt1228
Responsive Resident
7 years ago
Solved

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...
Need Help
  • jpt1228's avatar
    jpt1228
    7 years ago

    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.

     

     

Chihiro's avatar
Chihiro
Solution Sage
7 years ago

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's avatar
jpt1228
Responsive Resident
7 years ago

Chihiro I'm getting an error w/your formula

 

  • Chihiro's avatar
    Chihiro
    Solution Sage
    7 years ago
    ... 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's avatar
      jpt1228
      Responsive Resident
      7 years ago

      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.