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 cal...
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.

glearner's avatar
glearner
Frequent Visitor
1 year ago

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's avatar
danextian
Super User
1 year ago

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]
)