Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DennisSchlein
Helper II
Helper II

Newbie - Basic Cost/Sales question

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

CreditorNumberOrderTypeParcelNumberAmount
1100Freight123458
1100Surcharge1111111
2105Freight3216549
2105Surcharge2222221
2110Freight7894569
2110Surcharge88879791


Table:Sales

CreditorNumberTypeParcelNumberAmountSalesOrder
1Freight1234510100
1Surcharge1111111100
2Freight32165415105
2Surcharge2222222105



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:

CreditorNumberOrderTypeParcelNumberCostAmountSalesAmount
1100Freight12345810
1100Surcharge11111111
2105Freight321654915
2105Surcharge22222212
2110Freight78945690
2110Surcharge888797910


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

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


1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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)

View solution in original post

2 REPLIES 2
DennisSchlein
Helper II
Helper II

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

amitchandak
Super User
Super User

@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)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors