Apr 16 2022 04:02 AM
As you can see in the attached file. We have the current price and various offers. I want to calculate the following:
- The last price offered.
- The final price. This is the last price offered or, if this is missing, the current price.
With my measures 'Latest Offer' and 'Final Price' I can calculate the desired values.
The problem is the Grand Total, which shows an incorrect value for the 'Final Price'.
Apr 16 2022 05:11 AM - edited Apr 16 2022 05:23 AM
Solution@Roli_Bird What is it that you expect to see in the yellow shaded totals? The following measure will sum the final Prices column, resulting in 291.
=CALCULATE(sumx(tblPrices,IF([Latest Offer]<>BLANK(),[Latest Offer],[Actual Price])))
Apr 16 2022 07:46 AM
Apr 16 2022 07:54 AM
If do not show Grand Total for Latest Price
it could be
Latest Offer A:=
VAR maxDate =
MAX ( tblOffers[OfferDate] )
RETURN
IF (
ISFILTERED ( tblItems[ItemCode] ),
CALCULATE (
VALUES ( tblOffers[OfferedPrice] ),
tblOffers[OfferDate] = maxDate
),
BLANK ()
)
Final Price A:=
VAR price =
IF (
[Latest Offer A] = 0,
[Actual Price],
[Latest Offer A]
)
RETURN
IF (
ISFILTERED ( tblItems[ItemCode] ),
price,
SUMX (
tblItems,
IF (
[Latest Offer A] = 0,
[Actual Price],
[Latest Offer A]
)
)
)
Apr 16 2022 09:55 AM
That's a very good solution. I like how you structure your code. But when we filter the data, the result is no longer correct.
I modified your code as follows.
Latest Offer B:=VAR maxDate =
MAX ( tblOffers[OfferDate] )
RETURN
IF (
HASONEVALUE( tblItems[ItemCode] );
CALCULATE (
VALUES ( tblOffers[OfferedPrice] );
tblOffers[OfferDate] = maxDate
);
BLANK ()
)
Final Price B:=VAR price =
IF (
[Latest Offer A] = 0;
[Actual Price];
[Latest Offer A]
)
RETURN
IF (
HASONEVALUE( tblItems[ItemCode] );
price;
SUMX (
tblItems;
IF (
[Latest Offer A] = 0;
[Actual Price];
[Latest Offer A]
)
)
)
Now the result looks better. See also the attached file.
Please do not get me wrong. Your solution is great. I would never have come up with your solution and only made a small change to YOUR measure.
Thank you for your support and solution.
Apr 16 2022 10:04 AM
Yes, you are right, HASONVALUE() gives more correct result.
Apr 16 2022 05:11 AM - edited Apr 16 2022 05:23 AM
Solution@Roli_Bird What is it that you expect to see in the yellow shaded totals? The following measure will sum the final Prices column, resulting in 291.
=CALCULATE(sumx(tblPrices,IF([Latest Offer]<>BLANK(),[Latest Offer],[Actual Price])))