Calculate closest to?..

[Anonymous]

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 calculate which guidance color the actual sell was closest to in each month for each item. In excel, I would have it set up as such:

Month    Item    Sell Price    Green    Yellow    

In excel, I would do differences of each, then the minimum function, then an if then to figure out what the sell price was closest to. In BI, I flattened (sounds like this is always the route to go) so it looks like this:

Month    Item    Sell Price    Price Guidance Color

How can I calculate what I'm looking to do??

Thanks!

[Greg_Deckler]

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

[Anonymous]

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

[Greg_Deckler]

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.

[Anonymous]

https://www.dropbox.com/s/jxzfbfqjm5z3y2i/Sample.pbix?dl=0

[Vvelarde]

@Anonymous

I hope this help you:

PriceGuidanceColor = CALCULATE(VALUES(Sheet1[Guidance Color]);FILTER(Sheet1;Sheet1[Guidance Price]=MINX(Sheet1;Sheet1[Guidance Price]-Sheet1[Sell Price])+Minx(Sheet1;Sheet1[Sell Price])))

[Anonymous]

@Vvelarde

There is something off there. It won't allow me to put the measure in a table row or column. I would like to summarize by counting how many items per month are selling closer to GRN, closer to YLW, and closer to RED.

[v-micsh-msft]

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]

@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 · Accepted solution]

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]

@v-micsh-msft

Thank you. This worked great.