cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
twinrabbits
Advisor

Get the lowest cost and name from a group

I have a table with 3 columns.  I need to output the min cost for product by scenario and lowest cost product.

I have tried MINX and Grouping but I am unable to get all I need

 

Scenario.jpg

5 REPLIES 5
edhans New Contributor
New Contributor

Re: Get the lowest cost and name from a group

This will give you the minimum cost for each scenario. Is that what you are asking for?

Min Cost By Product and Scenario = 
CALCULATE(
    MIN('DataTable'[Total Cost]),
    ALL('DataTable'[Product])
)

Lowest product cost overall would be:

Lowest Cost = 
CALCULATE(
    MIN('DataTable'[Total Cost]),
    ALL('DataTable')
)

 20190227 13_08_50-Untitled - Power BI Desktop.png

twinrabbits
Advisor

Re: Get the lowest cost and name from a group

Thanks!!

This is what I need yes but the last part would be to filter so that only the lowest combinations

two.jpg

edhans New Contributor
New Contributor

Re: Get the lowest cost and name from a group

Add this measure

Lowest Cost Filter = (MAX('DataTable'[Total Cost]) = [Min Cost By Product and Scenario]) * 1

Then add that measure to a filter and set it to only show when it is 1. If it is zero, then it isn't the lowest cost for that Scenario/Product combo. You have to multiply by 1 to convert True/False to 1/0 so it will be filterable.

twinrabbits
Advisor

Re: Get the lowest cost and name from a group

For me this does not work becuase 

[Min Cost By Product and Scenario]

is not a measure but a column.  

edhans New Contributor
New Contributor

Re: Get the lowest cost and name from a group

A column how? A calculated column? It should be a measure. A column from Power Query? Why would you bring this data in this way as it won't recalculate as you filter/slice/add different contexts to a matrix.

 

It should be the measure I posted unless there is a really good reason to make it a column

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 375 members 3,278 guests
Please welcome our newest community members: