Average by group

[glearner]

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>?
 

[danextian · Accepted solution]

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]

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]

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.

[glearner]

@danextian  So you are suggesting that somewhere, somehow, the semantic model is filtering the fact table based on a field inside teh calendar table that is not datekey? Gosh I hate this model...

I was hoping to calculate this averages by store and delivery partner.

Would an all except DeliveryMetrics.StoreKey & DeliveryMetrics.PartnerKey be fit for purpose?

Is there a way to limit the All based on Date Selected in the Slicers? AllSelected(Date) perhaps?

Thansk for your ongoing support

[danextian]

There is a key difference between modifying the filter context on a column versus on a table. Using ALL('Table'[Column]) removes the filter specifically from that column. As long as that column is referenced, the filter is ignored. For example, if you include DateKey in a visual along with a measure that applies ALL to it, the same value will appear across all rows of the column, even if a slicer is applied to DateKey . In this case, only the visible rows will change based on the slicer selection, but the measure's values will remain consistent across the entire column due to the ALL function.

On the other hand, if you filter by actual dates using a slicer, the values will adjust to reflect the filtered range. However, the measure will still display the same value across the entire DateKey column because the ALL function was applied to that column specifically.

This highlights why it is considered best practice to use a separate date table. In denormalized tables where all data resides in a single table, applying ALL to the entire table will prevent the values from changing, regardless of the filters applied to it.

It is a practice for some to use DateKey in a relationship instead of the actual dates because that what it is in the source database.

Is there a way to limit the All based on Date Selected in the Slicers? AllSelected(Date) perhaps?

Yes, you can use ALLSELECTED to limit the scope of ALL based on the current selection in slicers or visuals. However, it’s important to understand how ALLSELECTED behaves. It respects the filters applied in the current context but removes lower-level filters within the hierarchy.

For example, if you add columns such as Date, Month, Quarter, and Year from a date table into a visual, and your measure uses ALLSELECTED, the values will remain consistent across all rows within the currently selected context. If you select Q1 in the slicer, the measure will show the total value for Q1 in all cells, regardless of the individual dates, months, or other lower-level filters.

This happens because ALLSELECTED retains the filters applied at higher levels (like Q1) while ignoring any finer-grained filters (like individual dates). It's useful when you need the measure to respect the slicer selection but aggregate at a broader level within that selection. For example, in cases when you want to compare the individual days against the total of all visible days.

[glearner]

Excellent @danextian, thanks so much.
We are making great progress, but clearly there is some context transitioning knowledge that eludes me, since using the allselected does not produce the intended final outcome.

I managed to get the $/Hour thanks to you, but now the sumx is producing an unexpected outcome, I was expecting to see 1.3 x 2  = 2.6 for the first store

Moreover, the card with the same measure also produces the incorrect results, as I was expecting it to sum said multiplication for each store and delivery partner combination.

Measures defined below:

1. Shift = Sum(DeliveryMetrics[Shift])

2. $/h: 

Var D_TotalOnlineHours = CALCULATE([D_OnlineHours],ALLSELECTED('Date'))

        Var D_TotalRevenue = CALCULATE([D_DeliverySales],ALLSELECTED('Date')) 

        Var D_SalesPerHour = DIVIDE(D_TotalRevenue,D_TotalOnlineHours,"")

        return D_SalesPerHour

multiplication:

SUMX(DeliveryMetrics,[Shift]*[$/h])

Thanks for your ongoing support; I sincerely hope that with your assistance I can understand how this works and put the report to rest

[danextian]

Hi @glearner 

SUMX(DeliveryMetrics, [Shift] * [$/h]) evaluates each row in the DeliveryMetrics table individually, not based on combinations of store and delivery partner. The expression [Shift] * [$/h] is calculated for every row in the table, potentially producing different results for each row, and these values are then summed across all visible rows in DeliveryMetrics.

If you want the calculation to consider combinations of specific columns, avoid using the entire table unless those columns are the only ones present, and their combinations are unique. Using the whole table can lead to unintended results if duplicate combinations exist and may cause a performance issue on large tables due to the sheet number or rows being iterated.

Try:

SUMX(
    // The SUMMARIZECOLUMNS function creates a table grouped by 'table'[column1] and 'table'[column2].
    // It also adds a virtual column named "@value" that contains the result of [the measure].
    SUMMARIZECOLUMNS(
        'table'[column1], 
        'table'[column2], 
        "@value", [the measure]
    ),
    // The SUMX function iterates through each row of the summarized table,
    // accessing the calculated column "@value" and summing its values.
    [@value]
)