Newbie - Basic Cost/Sales question

[DennisSchlein]

Hi 

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: Cost

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

Table:Sales

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

What 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->Sales on order and type
if not, then if there is a link between Cost-> Sales on Parcel number and type

So I end someling like this:

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

or, my assumption is, this is the way to do it?

Any help would be appriciated. I can provide what ever is needed.

[DennisSchlein]

Hi @amitchandak , ill take a look and get back to you 🙂

[amitchandak · Accepted solution]

@DennisSchlein , Create a new column in the sales table

new cost =

Var _max = maxx(filter(cost, cost[Order] = sales[Order] && cost[TYpe] = sales[Type]), cost[Amount])

var _max = maxx(filter(cost, cost[parcelNumber] = sales[parcelNumber] && cost[TYpe] = sales[Type]), cost[Amount])

return

coalesce(_max, _max1)

or new column in cost

new cost =

Var _max = maxx(filter(sales, cost[Order] = sales[Order] && cost[TYpe] = sales[Type]), sales[sales Amount])

var _max = maxx(filter(sales, cost[parcelNumber] = sales[parcelNumber] && cost[TYpe] = sales[Type]), sales[sales Amount])

return

coalesce(_max, _max1)