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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MarcRapenne
Frequent Visitor

Deaveraging prices

Hello! 

 

I am pretty new to Dax and need help with the below problem:

 

I have a series of products with unique serial numbers that have been bought in a LOT at an average price of X / Unit ( in the picture below it is $64.70)

Some of the serial numbers end up being non-fully functional (NF) and therefore we want to price them in our system at 50% discount. ie: $32.35

We would like Power BI to create a new column with an adjusted price: 

- for NF product = $32.35

- for all other product = a BuyPrice higher than $64.70 so that the average of the entire lot remains the same. In other words distribute the cumulated loss on the NF product to all other products. 

 

Thank you for your help!

Marc

7 REPLIES 7
V-lianl-msft
Community Support
Community Support

Hi @MarcRapenne ,

 

You can try the DAX like below:

Column = 
VAR COUNT_ID =CALCULATE( COUNT('Table'[ID]),FILTER('Table','Table'[ID]<>"NF"))
VAR SUM_PRICE =SUM('Table'[Price])
VAR _50 = 0.5*'Table'[Price]
RETURN IF('Table'[ID]="NF",_50,(SUM_PRICE-_50)/COUNT_ID)

test_50%.PNG

Sample .pbix

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler
Super User
Super User

Sounds doable. Unfortunately I can't really make heads or tails of exactly what to do with the current level of detail provided. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

 

No idea what your data looks like, what is and is not an NF product and a whole host of other unknowns.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

 

Hello @V-lianl-msft 

 

Thanks for your feedback. I was able to make it work !

 

In the file you can see 3 sheets (dropbox Link)https://www.dropbox.com/s/mc14wegnnubptd8/200416%20TEST%20Power%20BI.xlsx?dl=0 

 

  1. A sample file – with all units part of the same LOT (LOT1)
  2. The formula that we used – this is the same as the one posted by you – but I had to add Count_NF and use it in the return line (the example you posted had only 1 line NF, the formula would not work with more than 1 NF line)
  3. A new sample file with units being part of 2 Lots (LOT1 and LOT2)

 

The question I have now is how do I update my formula in order to make it work for a data base that has 2 Lots or more: the data base has units of 2 different lots but I need to run the calculations lot by lot, separately

 

Thanks so much ! 

Hi @MarcRapenne ,

 

I recreated pbix based on the data you provided.

Column = 
VAR COUNT_ID =CALCULATE( COUNT('Table'[PW Grade]),FILTER('Table','Table'[PW Grade]<>"NF"&&'Table'[LotNumber]=EARLIER('Table'[LotNumber])))
VAR COUNT_NF = CALCULATE( COUNT('Table'[PW Grade]),FILTER('Table','Table'[PW Grade]="NF"&&'Table'[LotNumber]=EARLIER('Table'[LotNumber])))
VAR SUM_PRICE =CALCULATE(SUM('Table'[BuyPrice]),FILTER('Table','Table'[LotNumber]=EARLIER('Table'[LotNumber])))
VAR _50 = 0.5*'Table'[BuyPrice]
RETURN IF('Table'[PW Grade]="NF",_50,(SUM_PRICE-_50*COUNT_NF)/COUNT_ID)

See if this meets your needs.

test_Deaveraging prices.PNG

Sample .pbix

 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @V-lianl-msft ,

 

Thank you, this worked great!

Now I am wondering if I can keep adding filters within"EARLIER". Because unfortunately, after looking at all my historical data, some lots have different items within them which therefore have a different Buy Price (example pictured) 

This messes up the Adjusted Price since the calculation is at LOT level. I tried to add another Filter (Model) but something doesn't seem to be working. 

Thank you again for all your help!

Marc 

 

Power BI.PNG

Hi @MarcRapenne ,

 

Yes, you can add appropriate filter conditions according to your own needs.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @V-lianl-msft ,

 

Thanks again. When I am trying to add the other filter to filter out the data via Model (after LOT) I get an error message. "Parameter is not the correct type". Please let me know if you have a solution for this.

 

Formula Below: 

AdjPrice =

VAR COUNT_GOOD =CALCULATE( COUNT(ComprehensiveInventory[FunctionalGrade]),FILTER('ComprehensiveInventory',ComprehensiveInventory[FunctionalGrade]<>"NF"&&ComprehensiveInventory[LotNumber]=EARLIER(ComprehensiveInventory[LotNumber] && ComprehensiveInventory[Model]=EARLIER(ComprehensiveInventory[Model])))

 

Thank you so much!

Marc

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors