Forum Discussion

pstp's avatar
pstp
Frequent Visitor
5 years ago
Solved

Date Dimension Table and Calculating Averages

I have a fact table with event attendance that has multiple data points for the same day/week (for different locations and times). I also have a master date dimension table. The two tables are related. In 2020, we had multiple weeks where we did not host events, and therefore have no data for those dates in the fact table. I'm trying to calculate average weekly attendance. When I use the formula

Average = SUM(Fact_Table[YValue]) / DISTINCTCOUNT(Dim_Date_Table[CalendarWeekNumber])

the distinct count returns 53, not the number of weeks that we have data for in the fact table. Is there a way to use the date table in the calculation, but for it to only use data for corresponding dates in the fact table? Thanks!

Need Help
  • nvprasad's avatar
    nvprasad
    5 years ago

    Hi 

    Can you try with belwo dax?

     

    Average = SUM(Fact_Table[YValue]) / CALCULATE(DISTINCTCOUNT(Dim_Date_Table[CalendarWeekNumber],FACTTABE[DATECOLUMN]))

    Appreciate a Kudos! 🙂
    If this helps and resolves the issue, please mark it as a Solution! 🙂

    Regards,
    N V Durga Prasad

1 Reply

  • nvprasad's avatar
    nvprasad
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi 

    Can you try with belwo dax?

     

    Average = SUM(Fact_Table[YValue]) / CALCULATE(DISTINCTCOUNT(Dim_Date_Table[CalendarWeekNumber],FACTTABE[DATECOLUMN]))

    Appreciate a Kudos! 🙂
    If this helps and resolves the issue, please mark it as a Solution! 🙂

    Regards,
    N V Durga Prasad