Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Calculate closest to?..

Hello,   I am working with a suggested pricing model for quoting coupled with actual sales data. For each item, there are three suggested prices, we will call them Green, Yellow, and Red. I want to...
Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    10 years ago

    Hi rynoh17,

    Yes, if month column didn't get single value, create another filter for the month:

    Mostrelated = if(ABS(Sheet1[Guidance Price]-Sheet1[Sell Price])= MinX(

                                                                        filter(Sheet1,

                                                                               And(Sheet1[Item]=earlier(Sheet1[Item]),

                                                                                        Sheet1[month]=earlier(Sheet1[Month])),

                                                                        ABS(Sheet1[Guidance Price]-Sheet1[Sell Price])

                                                                         ),

                    Sheet1[Guidance color])

    Check this out.

    If any further assistance needed, please feel free to post back.

    Regards

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

You could do it almost exactly the same way in Power BI and the formulas would pretty much be the same.

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    I can't do it that way when it is flat though.. Do I unflatten, or is there another way?

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      10 years ago

      Can you post some fake sample data? There's a solution, but it depends on the data and it is difficult to visualize what your data looks like.