Forum Discussion

Roli_Bird's avatar
Roli_Bird
Copper Contributor
Apr 16, 2022
Solved

Finding the last offered price

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'.

 

BI & Data Analysis
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Apr 16, 2022

    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])))

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 16, 2022

    Roli_Bird 

    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]
            )
        )
    )
    • Roli_Bird's avatar
      Roli_Bird
      Copper Contributor
      Apr 16, 2022

      SergeiBaklan 

      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.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 16, 2022

    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])))

    • Roli_Bird's avatar
      Roli_Bird
      Copper Contributor
      Apr 16, 2022
      Thank you very much. 291 is what I expect to see - and that's what your measure calculates.

      My next problem is just understanding your measure. You use CALCULATE, without filter and inside CALCULATE you use SUMX in table tblPrices with my original formula as expression.

      Could you please explain in two or three sentences how you came up with this measure or how the measure works.

Resources