aggregate fact table sales based on a dim table column that are related through another dimension ta

[arunaramana]

Hello There,

I have a situation, I want to get the reseller sales for "WASHINGTON REGION"; the database is "AdventureWorksDW2019" and I have the column relation as follows:

The geography details are populated in "DimGeography" table,

The reseller details are populated in "DimReseller" table, where each geography, say in my case, WASHINGTON could have more then reseller; hence, there is many-to-one relation between "DimReseller" and "DimGeography" and the column in relation is "GeographyKey"

The fact table "FactResellerSales" table is populated with reseller-sales; hence there is many-to-one relation between "FactResellerSales" and "DimReseller" tables; the column in relation is "ResellerKey"

    --and the fact table has indirectly relation with "DimGeography" table

I am struggling generate a DAX query to get reseller-sales total belonging to "Washington"; could anyone help me understand to achieve this?

Thank you for giving your valuable time; pls find the screenshot of the schema diagram attached to this email.

[AllisonKennedy · Accepted solution]

@arunaramana  If I understand your question correctly you don't need any extra DAX. 

Just put your [Total Sales] measure in a visual with DimGeography[City] where

Total Sales = SUM(FactResellerSales[SalesAmount] )

The relationships you have created will do the rest of the work. 

[v-xiaotang · Accepted solution]

Hi @arunaramana 

try the measure like this

Total Sales = 
SUMX (
    FILTER (
        FactResellerSales,
        RELATED ( DimGeography[StateProvinceCode] ) = SELECTEDVALUE ( DimGeography[StateProvinceCode] )
    ),
    FactResellerSales[SalesAmount]
)

I also created a sample file attached bellow, hope it helps.

FYI: 

Get a field value from a related table in Power BI: DAX RELATED Function Explained

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[v-xiaotang · Accepted solution]

Hi @arunaramana 

try the measure like this

Total Sales = 
SUMX (
    FILTER (
        FactResellerSales,
        RELATED ( DimGeography[StateProvinceCode] ) = SELECTEDVALUE ( DimGeography[StateProvinceCode] )
    ),
    FactResellerSales[SalesAmount]
)

I also created a sample file attached bellow, hope it helps.

FYI: 

Get a field value from a related table in Power BI: DAX RELATED Function Explained

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[arunaramana]

Hi, I have gone through the report file you shared me; I noticed that you have created (calculated) measure in DimGeography -- dimension table. I got a concern, under what circumstances we create measures in a dimension table? I am in preception that we usually create calculated measures in fact-tables. 

Could you please help me understand this concern? Thank you! 

[AllisonKennedy · Accepted solution]

@arunaramana  If I understand your question correctly you don't need any extra DAX. 

Just put your [Total Sales] measure in a visual with DimGeography[City] where

Total Sales = SUM(FactResellerSales[SalesAmount] )

The relationships you have created will do the rest of the work. 

[arunaramana]

Allison,

the solution you suggested is supposed to work, and but, it didn't work; I found the reason, and it is all because of the inactive-relation between the tables I wanted to perform the filtering and aggregation. Please find the attached screenshot. 

Thank you for your support! 

[AllisonKennedy · Accepted solution]

@arunaramana  Why is that relationship Inactive? Usually inactive relationships are only needed for examples like your DimDate where we have what's called 'role playing dimensions' that can be used for multiple columns in the same table (DueDate, Orderdate, ShipDate).

As for your question to @v-xiaotang  - it doesn't technically matter where you put the Measure - it can be stored in ANY table. Many developers will create a dedicated Key Measures table to store all their measures. If you follow the naming convention of always putting TableName[ColumnName] and only using [MeasureName] (with no TableName) in all your DAX formulas, you'll be able to easily move the 'Home table' of your measures and put them in any table that makes sense to you.

[arunaramana]

Hello AllisonKennedy, 

Thank you for responding on my concern; I tried the way you recommended me; but, I am getting repeated values in the sales-total; the filter isn't propogating. is it because, the fact table "FactResellerSales" doesn't have a direct relation with the "DimGeography" dimension? Please look to the screenshots to have glimpse of the matrix:

[AllisonKennedy]

@arunaramana  What table does StateProvinceCode come from?

[arunaramana]

the "state province code" column comes from "DimGeography" table which has the code "WA" for washington.