Join 2 tables and calculate different totals

[Meena]

Hi,

I have 2 tables as below. I have a requirement to calculate differeent totals based on different joins between the tables.

(1) Budget,Actual Sales by SalesRegion

(2) Budget,Actual Sales by Sales manager

(3) Budget, Actual Sales by Sales Region, product etc.

How to do it?

SalesRegion	Product	Budget
Asia	Bike	100
Asia	Car	100
Europe	Bike	300
USA	Bike	200
Canada	Car	200

SalesManager	SalesRegion	Product	Sales
ABC	Asia	Bike	5
ABC	Asia	Car	5
DEF	USA	Bike	50
XYZ	Canada	Car	10

[Anonymous · Accepted solution]

Hi @Meena ,

Here I suggest you to create inactive relationship between two tables.

Actual Sales by SalesRegion =
CALCULATE (
    SUM ( 'Table (2)'[Sales] ),
    USERELATIONSHIP ( 'Table'[SalesRegion], 'Table (2)'[SalesRegion] )
)

Actual Budget by Sales manager = 
VAR _ADD = ADDCOLUMNS('Table (2)',"Budget",CALCULATE(SUM('Table'[Budget]),FILTER('Table','Table'[SalesRegion] = EARLIER('Table (2)'[SalesRegion])&& 'Table'[Product] = EARLIER('Table (2)'[Product]))))
RETURN
SUMX(_ADD,[Budget])

Actual Sales by Sales Region, product = 
VAR _ADD = ADDCOLUMNS('Table',"Sales",CALCULATE(SUM('Table (2)'[Sales]),FILTER('Table (2)','Table (2)'[SalesRegion] = EARLIER('Table'[SalesRegion])&&'Table (2)'[Product] = EARLIER('Table'[Product]))))
RETURN
SUMX(_ADD,[Sales])

Result is as below.

Best Regards,
Rico Zhou

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