Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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:
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?
here the adjusted code:
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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!
Consignee | OuterProduct | Pieces | Qty Sent | Qty Invoiced | Weight | $/Unit | Gross Invoiced | Buyer Rebate | Freight | Packing | Marketing | Estimated_market_price | Estimated market price x 10 % | Estimated market price minus Estimated market price x 10 % | Marketing Discount Amount | Grower Return plus Estimated market price plus discount minus ETM 10% | Grower Return plus Estimated market price plus discount divided by Weight | Grower Return plus Estimated market price plus discount minus ETM 10% divided by Pieces |
Kalfresh | CLCR - Organic Green Bean - Loose - 10kg Crate | 0 | 5149 | 4077 | 51490 | $41.79 | $170,385.10 | $0.00 | $8,513.87 | ######## | ######## | $0.00 | $0.00 | $0.00 | $0.00 | $4,007.57 | $0.08 | |
A | PFST - Passionate Farmer - Organic - Green Beans - 10kg Styro | 0 | 468 | 256 | 4680 | $42.27 | $10,820.00 | $0.00 | $1,805.51 | ######## | $1,082.00 | $0.00 | $0.00 | $0.00 | $0.00 | ######## | -$1.17 | |
B | PFCTN - Passionate Farmer - Organic - Green Beans - 10kg Carton | 0 | 262 | 190 | 2620 | $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 | |
C | PFCTN - Passionate Farmer - Organic - Green Beans - 10kg Carton | 0 | 246 | 222 | 2460 | $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 | |
D | PFST - Passionate Farmer - Organic - Green Beans - 10kg Styro | 0 | 24 | 0 | 240 | $0.00 | $0.00 | $0.00 | $686.40 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | -$686.40 | -$2.86 | ||
E | PFCTN - Passionate Farmer - Organic - Green Beans - 10kg Carton | 0 | 16 | 16 | 160 | $60.00 | $960.00 | $0.00 | $84.67 | $457.60 | $96.00 | $0.00 | $0.00 | $0.00 | $0.00 | $321.73 | $2.01 |
If both calculation will run then we need to sum both the output?
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @Samarth_18
Yes thats right, we need to tolal them both up so the the end result.
i.e kalfresh priced at $10 and the rest priced at $20... need to total all values up.
Alright, then your final code would be as below:-
Estimated_market_price =
VAR _kalfres_data =
IF (
MAX ( Invoiced[Qty Invoiced] ) <> MAX ( Invoiced[Qty Sent] ),
IF (
MAX ( Invoiced[Consignee] ) = "Kalfresh",
SUM ( Invoiced[Estimated market price] )
)
)
VAR rest_data =
IF (
MAX ( Invoiced[Qty Invoiced] ) <> MAX ( Invoiced[Qty Sent] ),
SUM ( Invoiced[$/Unit] )
)
RETURN
_kalfres_data + rest_data
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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.
Consignee | OuterProduct | Pieces | Qty Sent | Qty Invoiced | Weight | $/Unit | Gross Invoiced | Buyer Rebate | Freight | Packing | Marketing | Estimated market price | Estimated market price x 10 % | Estimated market price minus Estimated market price x 10 % | Marketing Discount Amount | Grower Return plus Estimated market price plus discount minus ETM 10% | Grower Return plus Estimated market price plus discount divided by Weight | Grower Return plus Estimated market price plus discount minus ETM 10% divided by Pieces |
Kalfresh | CLCR - Organic Green Bean - Loose - 10kg Crate | 0 | 1072 | 0 | 10720 | $0.00 | $0.00 | $917.60 | $29,319.20 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | ######## | -$2.82 | ||
A | PFST - Passionate Farmer - Organic - Green Beans - 10kg Styro | 0 | 108 | 60 | 1080 | $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 | |
B | PFCTN - Passionate Farmer - Organic - Green Beans - 10kg Carton | 0 | 54 | 54 | 540 | $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 | |
C | PFCTN - Passionate Farmer - Organic - Green Beans - 10kg Carton | 0 | 36 | 0 | 360 | $0.00 | $0.00 | $0.00 | $1,029.60 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | ######## | -$2.86 |
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @cocoloco79
You can add another IF condition as shown below:-
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[ConsigneeName] ) = "ABC",<Your calculation if it is true> )
)
We can provide you more specific Answer if you could share some sample data with expected output.
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
@cocoloco79 , Try a measure like
IF(
MAX(Invoiced[Qty Invoiced])<>MIN(Invoiced[Qty Sent]),
if( max(invoiced[ConsigneeName]) = "ABC" ,
SELECTEDVALUE('Market Price/ unit'[Parameter])*SUM(Invoiced[Not Invoiced Qty 1]),
SELECTEDVALUE('Market Price/ unit'[Parameter])*SUM(Invoiced[Not Invoiced Qty])
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |