Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
twinrabbits
Advocate I
Advocate I

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
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks!!

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

two.jpg

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

For me this does not work becuase 

[Min Cost By Product and Scenario]

is not a measure but a column.  

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.