Need most and least profitable product name along with profit amount

[Anonymous]

Hi,

I have 3 tables in my data model.

i) Productlines

ii) Products

iii) Order_Details

I have calculated the measures "Sales", "Cost" & "Profit" using DAX foumulas. Now, I want to show the most profitable product and least profitable product for each product line.

I was able to create measures that show the profit amount of the most and least profitable products from each productline using MAXX and MINX functions,

Max Profit = MAXX(Products,[Profit])

Min Profit  = MINX(Products,[Profit])

They give me data as shown below,

But I want the product name as well with the profit amount. For example, for 'Classic Cars' productline, the most profitable product is '1992 Ferrari 360' (profit - $135,996.78) and the least profitable product is '1970 Chevy Chevelle' (profit - $13,696.95). 

How do I achieve this? How do I get the product names to show up along with the profit amount in the table? If it is possible to get both the name and the amount in the same column, that would be great, if not, 2 separate columns showing the name and amount would be OK too.

Thanks,

Sourav

[edhans · Accepted solution]

The following measures will do what you want I believe.

Max Car Profit:

Max Profit Car Name = 
VAR MaximumProfit = 
    CALCULATE(
        MAX(Cars[Profit]),
        REMOVEFILTERS(Cars[Car])
    )
VAR CarName =
    CALCULATE(
        MAX(Cars[Car]),
        FILTER(
            ALL(Cars),
            Cars[Profit] = MaximumProfit
        )
    )
RETURN
CarName & " - " & FORMAT(MaximumProfit,"Currency")

Min Car Profit: 

Min Profit Car Name = 
VAR MinimumProfit = 
    CALCULATE(
        MIN(Cars[Profit]),
        REMOVEFILTERS(Cars[Car])
    )
VAR CarName =
    CALCULATE(
        MAX(Cars[Car]),
        FILTER(
            ALL(Cars),
            Cars[Profit] = MinimumProfit
        )
    )
RETURN
CarName & " - " & FORMAT(MinimumProfit,"Currency")

I get a table that looks like this:

I didn't bother posting the measures for Max Profit and Min Profit as you have that figured out.

Note the part of the measure that determines the car name that says MAX(Cars[Car]) has nothing to do with the min/max value, but is simply converting the table field to a scalar value. Could use MIN in both functions fo that as well. It is another use for MIN/MAX vs determining the min/max values.

See my PBIX file if you want to play with it. You should be able to adapt to your needs.

[Anonymous]

Hi @edhans ,

Thanks for your help, your formula is working.

I just made a small change to it. Since my 'Profit' value was a measure, MAX was not working, so I had to use MAXX. Here are the formulas I used,

Most Profitable Product =

var MaxProfit = MAXX( Products, [Profit] )

var ProductName = CALCULATE( MAX( Products[productName] ), FILTER( Products, [Profit] = MaxProfit ) )

return ProductName & " (" & FORMAT( MaxProfit, "Currency" ) & ")"

Least Profitable Product =

var MinProfit = MINX( Products, [Profit] )

var ProductName = CALCULATE( MIN( Products[productName] ), FILTER( Products, [Profit] = MinProfit ) )

return ProductName & " (" & FORMAT( MinProfit, "Currency" ) & ")"

This is the result I got,

Thanks,

Sourav

[CheenuSing]

Hi @Anonymous 

Can you upload sampledata( not picture ) pbix and output expected to Google/One Drive and share the link here to find a solution.

Cheers

CheenuSing