Calculating Average

[SimonNU]

I have three tables: a fact table, a look up table, and a custom date table.

The following formula works correctly when a user selects one period using a slicer from the custom date table, but in reality is should average by the amount of periods selected. How would I do this?

Metric :=CALCULATE (
    SUM ( 'FCT_TABLE'[AMOUNT] ),
    'DIM_TABLE'[DESCRIPTION] = "Full-time equivalent"
)

 

[scottsen]

Typically i do something like AmtPerDay := DIVIDE([TotalMetric], COUNTROWS(Calendar)).

Depending on your scenario, dividing by DISTINCTCOUNT(Calendar[PeriodId]) could work?

 

[SimonNU]

Thanks Scott!

I don't know why but I was going down a much more difficult route to solve such a simple problem.

 

[SimonNU]

I'm encountering a problem with the above formula.

Say for example my data granularity is by period (it's actually weekly, but this is a clearer example). If I slice by just the Year dimension then the results are incorrect because it's dividing by 12 when infact I only have 10 periods of fact data.

The only solution is to create a period slicer and highlight up to period 10.

Basically, is there a way to prevent it from dividing by future dates that don't exist in my fact table?

My formulas are:

FTE:=DIVIDE(SUM('Timekeeper Filters'[fct_FTE]),COUNTROWS('Time Filters'))

FTE YTD:=CALCULATE (
    [FTE],
    FILTER (
        ALL ('Time Filters'),
                        'Time Filters'[YYYY] = MAX ( 'Time Filters'[YYYY] ) &&
        'Time Filters'[YYYYPPW] <= MAX ('Time Filters'[YYYYPPW] )
    )
)

 

[scottsen]

Ya, its easier if you have a trimmed calendar table (so the last date in calendar, matches the last day in sales or whatever). Probably easiest to just create a calc column on your calendar table IsFuture ... =Calendar[DateKey] > MAX(Facts[SalesDate]) sorta thing... then you can easily filter out IsFuture?