Forum Discussion
Return a category name associated with a minimum value
So I have some simple data, please see the below example:
| Date | Seller Name | Item Number | Price |
| Oct 26 | Jill | 123ABC | 10 |
| Oct 26 | Bob | 123ABC | 9 |
| Oct 26 | Jill | 456DEF | 20 |
| Oct 26 | Joe | 456DEF | 20 |
| Oct 27 | Bob | 123ABC | 9 |
| Oct 27 | Jill | 123ABC | 10 |
| Oct 27 | Joe | 456DEF | 19 |
| Oct 27 | Bob | 456DEF | 18 |
I want to return the Seller name associated with the lowest Price for each combination of Item Number and Date in a stacked column chart or in a table. I have a slicer on my dashboard for the Item Number so only one Item Number can be selected at a time. My chart or table will use the date for each column. Please see the below example bar chart.
This seems so simple. The date is segregated by the chart's x-axis and the item number is isolated by the slicer but I cannot figure out how to return the SellerName associated with the lowest price for each date/item number combo.
Anonymous Seems like a variation on lookup min/max like:
Measure = VAR __Table = SUMMARIZE('Table',[Date],[Seller Name],[Item Number],"__Price",MIN([Price]) VAR __Min = MINX(__Table,[__Price]) RETURN MAXX(FITLER(__Table,[__Price]=__Min),[Seller Name])Anonymous Use CONCATENATEX instead of MAXX
3 Replies
- Greg_Deckler
Community Champion
Anonymous Seems like a variation on lookup min/max like:
Measure = VAR __Table = SUMMARIZE('Table',[Date],[Seller Name],[Item Number],"__Price",MIN([Price]) VAR __Min = MINX(__Table,[__Price]) RETURN MAXX(FITLER(__Table,[__Price]=__Min),[Seller Name])Hello Greg_Deckler and thanks for your response. I got your calculation to work in a table which is good enough for me. Do you know how I can return the names of multiple sellers when they have the same minimum price? Referring to my above example on 26 Oct for item number 456DEF, I want to display Joe and Jill. Right now, the measure displays Joe or Jill. Thanks!
- Greg_Deckler
Anonymous Use CONCATENATEX instead of MAXX