How can i find minimum value among all the vendors corresponding to the same row

i have a dataset that is somewhat similar to this
image
and i want to find the minimum price for the item comparing all the vendors. I need the result as “{ITEM} - MINPRICE at VENDOR_NAME”
i have tried creating a calculated field ifelse(VENDOR1<VENDOR2,VENDOR1,VENDOR2<VENDOR1,VENDOR2) it worked returning the min value among the vendors but i have a couple of unsolved problems here, they are:

  1. Not only it should return the min value but it should also return the name of that vendor
  2. Here i have only compared 2 Vendors just to test if its working, but in the real data the number of vendors vary from ITEM to ITEM

I am very thankful for this community for helping newbies like me.
Thank you in advance

First of all, the dataset that you provided is not ideal for reporting, this needs to be unpivoted first. That will solve your problem of dealing with varying no. of Vendors by having a Fixed Metadata. This needs to be done before importing the data into Quicksight.

As far as the reporting is concerned, you need to find the minimum value at every item level. Level Aware Aggregation can help you with that. Once that is achieved that newly created column can be compared against the original column to create an indicator column which can be leveraged to keep the relevant rows which will provide you the right details. Please see the snapshot below for your reference. Hope this helps. In case this solves your query, please mark this as the Answer. Thank you!

2 Likes

Thank you very much for the response. The answer you provided does work very efficiently, i will try to convey this solution to my superiors and will ask if they can unpivot the data in the warehouse.
Thank you very much

1 Like