Consolidated Company Revenue Using DAX

[Anonymous]

Hi,

First of all, let me explain what type of data structure I'm dealing with. I work for a cross border logistics company. Therefore, the data model is constructed from two sources (2 companies) which can be separated by the Company Code (JWDA, JWDAC). I would like to show the consolidated revenue that came from both sides. There is a column named " Master Job No " which links the jobs of JWDA and JWDAC together.

 

As you can see from the picture above, I would like to console the Total Revenue, Expense, and Gross Profit by using Master Job Number. For instance, The total revenue both rows of the Master Job No. JTH-2005-0050 must be $2052.35 ( which is 1700 + 352.35 ). Moreover, I would like to use the Shipper Name and Consignee Name as slicers. As I mentioned before, the data model was constructed with the data of two companies. So, the Shipper Name and Consignee Name of both sides are not the same. To avoid the confusion, I also would like to filter the slicers of shipper and consignee names to be from only JWDA as shown in the picture below.

This is what I have tried

End-to-End Revenue = 
IF(HASONEVALUE(Shipment[Master Job No]), 
CALCULATE( [Total Revenue], 
    ALL(Shipment[Shipper Name]),  
    ALL('Company Master'[Company ID])), 

CALCULATE(
    SUMX(VALUES(Shipment[Master Job No]),
        CALCULATE( [Total Revenue], 
            ALL(Shipment[Shipper Name]),  
            ALL('Company Master'[Company ID])))))

And the output is shown in the picture below.  But when I use the slicers it seems not correct.

 

For your further information, I have attached the dummy file to this link.

https://drive.google.com/file/d/1i1rsWKmzwcdsUK375BYrxN-fqk-iQzLg/view?usp=sharing 

Thank you in advance,

Paniti

[TomMartens · Accepted solution]

Hey @Anonymous ,

I would create two new calculated columns shipper (JDWA) and consignee (JDWA).

The DAX for the calculated column shipper (JDWA) would be similar to this:

shipper (JDWA) = 
if('tablename'[Company Code] = "JDWA"
,'tablename'[Shipper name]
,var __MasterJobNo = 'tablename'[Master Job No]
var shipperJDWA = LOOKUPVALUE(
	'tablename'[Shipper Name] ,
	'tablename'[Company Code] , "JDWA"
	'tablename'[Master Job No] , __MasterJobNo
)
return
if(ISBLANK(shipperJDWA), 'tablename'[Shipper Name] , __MasterJobNo)

Then you can use both new columns as slicer, using the value from the JDWA row.

Hopefully, this provides what you are looking for.

Regards,

Tom

[Anonymous]

Dear @TomMartens 

Thank you for your support. Your suggestion got me to the right answer.

Thank you and best regards,

Paniti