How to get most productive name with the value?

[Anonymous]

I have made a table visual with following fields and measure. 

1. Project (Field value)

2. Total Hours Worked = sum(Hours)

3. Total Productive Hours = sum(Productive Hours)

I want to get the name of the project which has the highest value of (Total Productive Hours/Total Hours) along with that value in card visual.

Eg (Expected Output) : 0.95 

I am able to get the highest value of  (Total Productive Hours/Total Hours) with the following measure but how to fetch project name pertaining to that value?

Most Productive Project =
VAR summarizedTable =
CALCULATETABLE (
SUMMARIZE (
'Employee Details',
'Employee Details'[Project],
"divides", DIVIDE ( [Total Productive Hours], [Total Hours Worked], 0 )
),
'Employee Details'[Project] <> "Free Time"
)
RETURN
MAXX ( summarizedTable, [divides] )

I got following result with this measure:

How do I fetch project name pertaining to this value?? Like Eg:-  0.95 

Any help is appreciated. Thanks.

[Nathaniel_C]

Hi @Anonymous ,
Run your measure twice, once for the value and once for the name.  Then create a measure that combines the name and the value like Access: 95.  This will be the result of your measure which will go into your card, and you will turn off the category name.  I use this all the time to mark an update time in a report.  "Report last updated: May 5"

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Anonymous]

@Nathaniel_C  I had tried creating a measure for project name :

Most Productive Project Name = calculate(max('Employee Details'[Project]), 'Employee Details'[Project]=Most Productive Project

where 'Most Productive Project' is the max value as mentioned in my question.

But this method didn't work!

[Nathaniel_C]

@Anonymous , Running out the door to an appointment, but try these measures. I created the percentage column in Power Query.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Most productive percentage = 
VAR _Percent =
    MAX ( Productive[Percentage1] )
return _Percent
============================================
Most productive name = 
VAR _Percent =
    MAX ( Productive[Percentage1] )
VAR _name =
    CALCULATE (
        MAX ( Productive[Project] ),
        FILTER ( ALL ( Productive ), Productive[Percentage1] = _Percent )
    )
RETURN
    _name
=============================================================
Combine 2 measures = Concatenate(CONCATENATE([Most productive name]," is the most productive project  "  ),[Most productive percentage])

[Anonymous]

Thanks for the reply.

But you have added percentage as a seperate column in the table. I am supposed to achieve the result without the need to add any extra column.

[Nathaniel_C]

Hi @Anonymous , use MAXX instead of MAX

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Most productive name = 
VAR _Percent =
   maxx(Productive,DIVIDE(Productive[Productive Hours],Productive[Hours]))
VAR _name =
    CALCULATE (
        MAX ( Productive[Project] ),
        FILTER ( ALL ( Productive ), Productive[Percentage1] = _Percent )
    )
RETURN
    _name
===============================
Most productive percentage = 
VAR _Percent =
    maxx(Productive,DIVIDE(Productive[Productive Hours],Productive[Hours]))

    
return _Percent

[Nathaniel_C · Accepted solution]

Hi @Anonymous 

Most productive name = 
VAR _Percent =
   maxx(Productive,DIVIDE(Productive[Productive Hours],Productive[Hours]))  //This figures the max value
VAR _name =
    CALCULATE (
        MAX ( Productive[Project] ),
        FILTER ( ALL ( Productive ),DIVIDE(Productive[Productive Hours],Productive[Hours])= _Percent)
    )
RETURN
    _name

Try this, this figures the max value, outside of the calculate, and then goes through the table row by row in the calculate function
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel