Hi,
I have been trying my google powers on this subject, but i've come up short. The best i have found is something about nesting ranked index inside grouping a table. But i cannot figure out how to apply to my specific challenge.
I have a table like this, many sites many products, sales value pr product. I need to find the lowest (-800) sales pr site and still show which product that is.
or in other words.. find which product a given site has the lowest sales with.
| site | month | product | sales |
| 1231 | mar | 145008 | 300 |
| 1231 | mar | 146012 | -400 |
| 1145 | mar | 145008 | 200 |
| 1145 | mar | 147445 | -100 |
| 1145 | mar | 111009 | -800 |
turned into:
| site | month | product | sales |
| 1231 | mar | 146012 | -400 |
| 1145 | mar | 111009 | -800 |
inside the query editor, before loading into the datamodel.
kind regards
Michael
I think i just found a solution... it's all about what you google!
https://excelgorilla.com/power-bi/power-query/reach-underlying-rows-using-table-max/
i'm just about to test it.
Worked like a charm.
Group by sites,
Min Sites, added a column called Details with all rows.
Then a calculated column Table.Min([Details], "Sales") which results in a column with records expanded that with only the product nr.
hope someone else finds this useful.
Exactly what I came here looking for. Thank you! I am attempting to identify the the rows that have the highest FTE for students that are duplicated in multiple school districts. So when I group by student ID and then use the formulas you describe I can identify which district the student is enrolled in for the most amount of time. Perfect!
Awesome!
Glad my search could help, it was very difficult to google a solution for this specific issue.
