Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 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
    9 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

v-micsh-msft's avatar
v-micsh-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi rynoh17,

 

If you would like to count the item number, then we could write a calculated column to mark the closet color with the proper color value:

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

                                                                    filter(Sheet1,

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

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

                                                                     ),

                Sheet1[Guidance color])

See my result based on the sample PBIX you uploaded:

 

 

Then write the count measure with the following:

Num = countrows(filter(Sheet1,Sheet1[Mostrelated]=Sheet1[Guidance Color]))

 

Please post back if any further assistance needed.

Regards

 

Anonymous's avatar
Anonymous
Not applicable
9 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
    9 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
    9 years ago

    v-micsh-msft

     

    Thank you. This worked great.