Forum Discussion

glearner's avatar
glearner
Frequent Visitor
1 year ago
Solved

Average by group

Hi 

 

I have a fact table that contains online hours by day, store, and partner platform.

The fact tables has a 1:* relationship coming from Dims Storekey,PartnerKey, and DateKey

 

How can I calculate the total online hours by store & partner, across the entire selected date range (via slicer - not influenced by the visual)

 

I've tried with several variations of ALL filters by clearly my knowledge is limited and I fail to produce the intended outcome.

 

Once I get the total online hours I would like to use it to caclulate the total Sales by Hour (I'll replicate the approach for total sales by Hour) which will ultimately give me the Average Sales by Hour per group; ultimately multiplying this by another measure the offline hours and producing the Lost Sales due to Downtime.

 

Can anyone assist me in produce the sales by partner & Store across the entire selected date range>?
 

 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi glearner 

     

    The measure below will remove any filters coming from the Dates/Calendar table but will respect the filter from other tables.

    // Calculates the total online hours across all dates, ignoring any filters on the Dates table.
CALCULATE([Online Hours Measure], ALL(Dates))

    For this measure to work, you must be using a separate Dates dimension table with a one-to-many single direction relationship to your fact table.

7 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi glearner 

     

    The measure below will remove any filters coming from the Dates/Calendar table but will respect the filter from other tables.

    // Calculates the total online hours across all dates, ignoring any filters on the Dates table.
CALCULATE([Online Hours Measure], ALL(Dates))

    For this measure to work, you must be using a separate Dates dimension table with a one-to-many single direction relationship to your fact table.

    • glearner's avatar
      glearner
      Frequent Visitor
      1 year ago

      Hi danextian , I feel dumb

       

      Using the All Date provides the intended output, however I was using All('Date'[DateKey]) which didn't.

       

      Do you have any idea why ? Confirming the relationship with the date table is a 1 to *, based on datekey.

       

       

      see below for a screenshot

      Thanks!

      • danextian's avatar
        danextian
        Super User
        1 year ago

        Using ALL('Date'[DateKey]), the filter modifier is applied exclusively to the DateKey column of the Date table. This means that filters specifically targeting the DateKey column are removed, while filters from other columns within the Date table or from any related tables will still remain active and influence the calculation. So if you use the actual Date column instead of the DateKey column, filters will still apply.