Reference fact/data table from Dimension table

[Lightice83]

Hi,

I'm doing the Mavern Analytics advanced DAX course & there is a example where we create a calculated column in the Fact/Lookup table for Customers to return total revenue (for all customers) from the Sales table Fact/Data table (this is being used as a input to a bigger calculation) into the Dimension/Lookup table

The calculation used is SUMX (see below) but would this not calculate the revenue per customer not all customers?  Or is it because there has been no context transition (starting with row context & not used calculate) hence it is just doing the SUMX over every customer?

Thanks

[DataInsights · Accepted solution]

@Lightice83,

The SUMX in the example would return the same amount for each customer, because it is iterating the entire fact table without regard to the relationship between Customers and Sales by Store. However, if you add RELATEDTABLE it will filter the fact table per the relationship with Customers:

SUMX (
    RELATEDTABLE ( 'Sales by Store' ),
    'Sales by Store'[quantity_sold] * 'Sales by Store'[unit_price]
)