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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

v-micsh-msft

 

That looks good.. So my actual data has multiple months. I would need another filter for that too, correct? Instead of having an item show up 3 times (GYR), it would show up 24 (GYR, Jan-Aug). What would I need to add?

v-micsh-msft's avatar
v-micsh-msft
Icon for Microsoft Employee rankMicrosoft Employee
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    v-micsh-msft

     

    Thank you. This worked great.