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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Get last sales price on my items in a new column

Hi everyone

 

I have 2 tables.

One is a simple table of my items and descriptions of them.

The other is a table of sales made. It looks like this

ItemDescriptionQuantitySales AmountDate
001Bike10200026-04-2021
001Bike10200027-04-2021
001Bike20300027-04-2021
002E-Bike10500026-04-2021
002E-Bike10450027-04-2021

I need to create a new column on my items list that shows the last sales price per unit for each item.

If several sales has happened on the same day for a single item i need the one with the largest price per unit. For example item 001. There are 2 sales on the 27th, and even though one sold for 3000, the other one has a better price per unit 2000/10 = 200.

 

In the end id like my Items table to look like this:

Item No.DescriptionLast Sales Price Per Unit
001Bike200 (2000/10)
002E-Bike450 (4500/10)
etc.etc.etc.

 

Any and all help is appreciated

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below for creating a column.

 

Picture2.png

 

 

Sales Price Per Unit =
VAR currentdescription = Items[Description]
VAR maxdate =
MAXX ( FILTER ( Sales, Items[Description] = currentdescription ), Sales[Date] )
VAR groupbyunitprice =
GROUPBY (
FILTER ( Sales, Sales[Description] = currentdescription ),
Sales[Date],
"@unitprice", MAXX ( CURRENTGROUP (), Sales[Sales Amount] / Sales[Quantity] )
)
VAR findmaxdateunitprice =
FILTER ( groupbyunitprice, Sales[Date] = maxdate )
RETURN
SUMX ( findmaxdateunitprice, [@unitprice] )

 

 

https://www.dropbox.com/s/wlr878f1lijwsth/hede.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below for creating a column.

 

Picture2.png

 

 

Sales Price Per Unit =
VAR currentdescription = Items[Description]
VAR maxdate =
MAXX ( FILTER ( Sales, Items[Description] = currentdescription ), Sales[Date] )
VAR groupbyunitprice =
GROUPBY (
FILTER ( Sales, Sales[Description] = currentdescription ),
Sales[Date],
"@unitprice", MAXX ( CURRENTGROUP (), Sales[Sales Amount] / Sales[Quantity] )
)
VAR findmaxdateunitprice =
FILTER ( groupbyunitprice, Sales[Date] = maxdate )
RETURN
SUMX ( findmaxdateunitprice, [@unitprice] )

 

 

https://www.dropbox.com/s/wlr878f1lijwsth/hede.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

I am having a hard time even understanding the solution, but it works like a charm.

Thank you so much. 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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