Forum Discussion
Calculate closest to?..
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
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.
Microsoft EmployeeHi 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
- v-micsh-msft
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
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?