I have two companies in the same fact table, which have intercompany sales.
To eliminate the IC margin in my report, I would like to replace the COGS value from the company selling to the end customer (Co.1) with the COGS value from the supplying company (Co.2).
The SO Number is the same for the sale from Co.2 => Co.1 => End customer
Probably not best practise, but I created a concatenated column using the product key, sales quantity and SO number so there is a relationship for the SKU sale in each company.
As I only want the sales numbers from Company 1, I have a slicer on the Company name.
I am trying to work out the DAX calculation to do this.
IC COGS = VAR Co2COGs =calculate( [COGS],
filter (ALL(sales), Sales[Company Key]=2))
And then can I create a Table for only Company 2,
and then use RELATED and Concate column to return Company 2 Cogs?
The desired end result below, and PBIX file attached.
Thanks in advance for your help, any suggestions or perhaps using ““Concat”” in the DAX to create the Concat column would be greatly appreciated!
COGS Test.pbix (474.3 KB)