Calculating totals by region across multiple tables

[Twotone]

Hi,

I'm trying to perform a fairly simple summary of sales data by region. 

The sales data resides on a table which summarises the sales by product and the postcode (Zip Code) in which the sale occured.  A separate table lists the Regions by postcode (many to many relationship) and the two tables are linked via a third 'postcode' table.   

If I summarise the sales by product, I get the results displayed as I would expect, with different totals per product (LHS).  But if I substitute the product for the region, each separate region displays the grand total of all sales, and not the total sales per region (RHS).  

As there is a many to many relationship between the region and the postcode, there will be some duplicate postcode values and hence the grand total will be higher than the total sales values we see above.  

I assume I need to use a DAX to deaggregate the sales data, but I haven't been able to work out which one; they all end up giving the same results.  

With thanks

Anthony

[v-yulgu-msft · Accepted solution]

Hi @Twotone,

Please set the "Cross Filter direction" to "Both" for the two relationships from region table to postcode table and from sales table to postcode table.

Best regards,

Yuliana Gu

[Twotone]

Hi @v-yulgu-msft, 

Sorry for the delay, but I don't get to work on these issues every day.

Thank you that worked!  Much appreciated; I read a lot of online tutorials and they always brushed over the different ways to join tables.  

Much appreciate!  

Anthony

[Twotone]

Ahhh!  Thanks @v-yulgu-msft!  That looks like a key aspect of the relational table set-up that I have missed.  I'll try this out in the coming days.  

[rajulshah]

Hello @Twotone,

Can you please the sample data file?

[Twotone]

Thanks Rajulshah,

[rajulshah]

Hello @Twotone,

May I know how the first table and second table is related to each other?

[Twotone]

The scond table is simply the 'postcode' table, which has unique values for each postcode.  I would be unable to link the sales data directly to the district data, because there are multiple rows with the same postcode in each of those tables.

[rajulshah]

You can just create a calculated column in the third table looking up for State value using Postcode Column of that table.

Hope this helps.

[Twotone]

Thanks Rajulshah,

Is it really necessary to create a column?  I thought this was bad practice where there was such a large number of variables and where it can be calculated via a measure.