Forum Discussion

cocoloco79's avatar
cocoloco79
Helper III
5 years ago
Solved

Parameter with condition

Hi everyone   I need to add a second parameter which will kick in if (invoiced[ConsigneeName]) is "ABC" to apply a diffrenet price. Can someone assist with this? Much appreciated!!! thank you!  ...
Need Help
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    Hi cocoloco79 

     

    Based on your all inputs. Below code would required to get desired output:-

     

    Estimated_market_price =
VAR result =
    IF (
        MAX ( Invoiced[Qty Invoiced] ) <> MAX ( Invoiced[Qty Sent] ),
        IF (
            MAX ( Invoiced[Consignee] ) = "Kalfresh",
            SUM ( Invoiced[Estimated market price] ),
            SUM ( Invoiced[$/Unit] )
        )
    )
RETURN
    IF ( result = 0, 0, result )

     

    Output:-

    Please let me know if it works for you.

     

    Thanks,

    Samarth

cocoloco79's avatar
cocoloco79
Helper III
5 years ago

Hi Samarth_18 and amitchandak 

 

I have used the measure below, but If use the Estimated Market Price Kalfresh' parameter then the calculation doesn't kick in.

However if I use the 'Market Price/ unit' the calculation works, but also includes Kalfresh.

 

Here is the Measure: 

Estimated market price =
IF(
MAX(Invoiced[Qty Invoiced])<>MIN(Invoiced[Qty Sent]),
SELECTEDVALUE('Market Price/ unit'[Parameter])*SUM(Invoiced[Not Invoiced Qty]),IF(MAX(Invoiced[Market])="Kalfresh" ,SELECTEDVALUE('Estimated Market Price Kalfresh'[Estimated Market Price Kalfresh])*SUM(Invoiced[Not Invoiced Qty])
))
 
I have uploaded some sample data here sample data 
 
Your help is much appreciated. Thank you!
  • Samarth_18's avatar
    Samarth_18
    Community Champion
    5 years ago

    Hi cocoloco79 ,

     

    Sample data link is not working for me. Could you please paste some sample data here if it is not sensitive and also what is the output you are getting with expected output data. Measure code looks fine to me I can check if you could provide above required details.

     

    Thanks,

    Samarth

    • cocoloco79's avatar
      cocoloco79
      Helper III
      5 years ago

      Hi Samarth_18 

       

      desired output would be that if market is "Kalfresh" the estimated market price is ignored and instead Estimated Market Price Kalfresh' is used. So in a nutshell, A,B,C will look at the Market Price/ unit' and Kalfresh will look at Estimated Market Price Kalfresh' IF there is a difference in Qty invoiced.

       

      ConsigneeOuterProductPiecesQty SentQty InvoicedWeight$/UnitGross InvoicedBuyer RebateFreightPackingMarketingEstimated market priceEstimated market price x 10 %Estimated market price minus Estimated market price x 10 %Marketing Discount AmountGrower Return plus Estimated market price plus discount minus ETM 10%Grower Return plus Estimated market price plus discount divided by WeightGrower Return plus Estimated market price plus discount minus ETM 10% divided by Pieces
      KalfreshCLCR - Organic Green Bean - Loose - 10kg Crate01072010720 $0.00$0.00$917.60$29,319.20$0.00$0.00$0.00$0.00$0.00########-$2.82 
      APFST - Passionate Farmer - Organic - Green Beans - 10kg Styro0108601080$45.00$2,700.00$0.00$258.00$3,088.80$270.00$0.00$0.00$0.00$0.00-$916.80-$0.85 
      BPFCTN - Passionate Farmer - Organic - Green Beans - 10kg Carton05454540$45.00$2,430.00$0.00$0.00$1,544.40$243.00$0.00$0.00$0.00$0.00$642.60$1.19 
      CPFCTN - Passionate Farmer - Organic - Green Beans - 10kg Carton0360360 $0.00$0.00$0.00$1,029.60$0.00$0.00$0.00$0.00$0.00########-$2.86 
      • Samarth_18's avatar
        Samarth_18
        Community Champion
        5 years ago

        Hi cocoloco79 

         

        Based on your all inputs. Below code would required to get desired output:-

         

        Estimated_market_price =
VAR result =
    IF (
        MAX ( Invoiced[Qty Invoiced] ) <> MAX ( Invoiced[Qty Sent] ),
        IF (
            MAX ( Invoiced[Consignee] ) = "Kalfresh",
            SUM ( Invoiced[Estimated market price] ),
            SUM ( Invoiced[$/Unit] )
        )
    )
RETURN
    IF ( result = 0, 0, result )

         

        Output:-

        Please let me know if it works for you.

         

        Thanks,

        Samarth

  • cocoloco79's avatar
    cocoloco79
    Helper III
    5 years ago

    Hi Samarth_18 

     

    I just realised that when some of the sent qty is not equal the invoiced qty, the code doesnt pick up the difference and therefore only works if invoice qty is "0".

    Could you please have another look at the formula?

    I belive the error is releated to the IF clause : IF ( result = 0, 0, result )

    here the adjusted code:

    Estimated_market_price =
    VAR result =
    IF (
    MAX ( Invoiced[Qty Invoiced] ) <> MAX ( Invoiced[Qty Sent] ),
    IF (
    MAX ( Invoiced[Market] ) = "Kalfresh",
    SELECTEDVALUE('Estimated Market Price Kalfresh'[Estimated Market Price Kalfresh])*SUM(Invoiced[Not Invoiced Qty]),
    SELECTEDVALUE('Market Price/ unit'[Parameter])*SUM(Invoiced[Not Invoiced Qty])
    )
    )
    RETURN
    IF ( result = 0, 0, result )
    • Samarth_18's avatar
      Samarth_18
      Community Champion
      5 years ago

      Hi cocoloco79 ,

       

      Just be on the same page, could you please confirm me on below points:-

      1. Our first condition would if Invoiced[Qty Invoiced]  is not equal to Invoiced[Qty Sent] and  Invoiced[Market]  is equal to "Kalfresh" then this condition should kicks in 

      "SELECTEDVALUE('Estimated Market Price Kalfresh'[Estimated Market Price Kalfresh])*SUM(Invoiced[Not Invoiced Qty]),"

       

      2. Our next condition would if Invoiced[Qty Invoiced]  is not equal to Invoiced[Qty Sent] and  Invoiced[Market]  is not equal to "Kalfresh" then this condition should kicks in 

      "SELECTEDVALUE('Market Price/ unit'[Parameter])*SUM(Invoiced[Not Invoiced Qty])"

       

      Please correct me if i am wrong.

      And also kindly share what output you are currently getting and what would be expected.

       

      In the meantime you can try with replace last two line with below code

       

      RETURN result

       

      Thanks,

      Samarth

      • cocoloco79's avatar
        cocoloco79
        Helper III
        5 years ago

        Hi Samarth_18 

         

        Yes, we are on the same page.

        Can both calulation be run at the same time if those 2 conditions are met?

         

        I have applied the two lines "Return result" but that didnt return the right resluts.

         

        Thank you!

         

        ConsigneeOuterProductPiecesQty SentQty InvoicedWeight$/UnitGross InvoicedBuyer RebateFreightPackingMarketingEstimated_market_priceEstimated market price x 10 %Estimated market price minus Estimated market price x 10 %Marketing Discount AmountGrower Return plus Estimated market price plus discount minus ETM 10%Grower Return plus Estimated market price plus discount divided by WeightGrower Return plus Estimated market price plus discount minus ETM 10% divided by Pieces
        KalfreshCLCR - Organic Green Bean - Loose - 10kg Crate05149407751490$41.79$170,385.10$0.00$8,513.87################$0.00$0.00$0.00$0.00$4,007.57$0.08 
        APFST - Passionate Farmer - Organic - Green Beans - 10kg Styro04682564680$42.27$10,820.00$0.00$1,805.51########$1,082.00$0.00$0.00$0.00$0.00########-$1.17 
        BPFCTN - Passionate Farmer - Organic - Green Beans - 10kg Carton02621902620$40.00$7,600.00$0.00$320.25$7,493.20$760.00$0.00$0.00$0.00$0.00-$973.45-$0.37 
        CPFCTN - Passionate Farmer - Organic - Green Beans - 10kg Carton02462222460$45.00$9,990.00$0.00$117.99$7,035.60$999.00$0.00$0.00$0.00$0.00$1,837.41$0.75 
        DPFST - Passionate Farmer - Organic - Green Beans - 10kg Styro0240240 $0.00$0.00$0.00$686.40$0.00$0.00$0.00$0.00$0.00-$686.40-$2.86 
        EPFCTN - Passionate Farmer - Organic - Green Beans - 10kg Carton01616160$60.00$960.00$0.00$84.67$457.60$96.00$0.00$0.00$0.00$0.00$321.73$2.01