Forum Discussion

MarcRapenne's avatar
MarcRapenne
Frequent Visitor
6 years ago

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

Need Help

7 Replies

    • MarcRapenne's avatar
      MarcRapenne
      Frequent Visitor
      6 years ago

       

      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 ! 

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        6 years ago

        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.

        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.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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)

    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.