Newbie - Basic Cost/Sales question

Question

Hi&nbsp;I have worked on structuring my data, to work in my power bi model.But working with it now, im not sure acuatly how to do it.The essense if I have 2 fact tables - Cost and Sales.table: CostCreditorNumberOrderTypeParcelNumberAmount1100Freight1234581100Surcharge11111112105Freight32165492105Surcharge22222212110Freight78945692110Surcharge88879791Table:SalesCreditorNumberTypeParcelNumberAmountSalesOrder1Freight12345101001Surcharge11111111002Freight321654151052Surcharge2222222105What im trying to do, it comparing my amount of cost to my amount of sales.But freight and surcharge is sold out of diffenret flows.So I want first to check if there is a match between Cost-&gt;Sales on order and typeif not, then if there is a link between Cost-&gt; Sales on Parcel number and typeSo I end someling like this:CreditorNumberOrderTypeParcelNumberCostAmountSalesAmount1100Freight123458101100Surcharge111111112105Freight3216549152105Surcharge222222122110Freight789456902110Surcharge888797910or, my assumption is, this is the way to do it?Any help would be appriciated. I can provide what ever is needed.

amitchandak · Accepted Answer

DennisSchlein , Create a new column in the sales table
&nbsp;
new cost =
Var _max = maxx(filter(cost, cost[Order] = sales[Order] &amp;&amp; cost[TYpe] = sales[Type]), cost[Amount])
var _max = maxx(filter(cost, cost[parcelNumber] = sales[parcelNumber] &amp;&amp; cost[TYpe] = sales[Type]), cost[Amount])
return
coalesce(_max, _max1)
&nbsp;
or new column in cost
&nbsp;
new cost =
Var _max = maxx(filter(sales, cost[Order] = sales[Order] &amp;&amp; cost[TYpe] = sales[Type]), sales[sales Amount])
var _max = maxx(filter(sales, cost[parcelNumber] = sales[parcelNumber] &amp;&amp; cost[TYpe] = sales[Type]), sales[sales Amount])
return
coalesce(_max, _max1)

CreditorNumber	Order	Type	ParcelNumber	Amount
1	100	Freight	12345	8
1	100	Surcharge	111111	1
2	105	Freight	321654	9
2	105	Surcharge	222222	1
2	110	Freight	789456	9
2	110	Surcharge	8887979	1

CreditorNumber	Type	ParcelNumber	AmountSales	Order
1	Freight	12345	10	100
1	Surcharge	111111	1	100
2	Freight	321654	15	105
2	Surcharge	222222	2	105

Forum Discussion

Newbie - Basic Cost/Sales question

2 Replies

Related content

Newbie question

Newbie Question for the Filter

Newbie Question Help

Newbie DAX question

antoher newbie question

CreditorNumber	Order	Type	ParcelNumber	CostAmount	SalesAmount
1	100	Freight	12345	8	10
1	100	Surcharge	111111	1	1
2	105	Freight	321654	9	15
2	105	Surcharge	222222	1	2
2	110	Freight	789456	9	0
2	110	Surcharge	8887979	1	0