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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.