Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Return largest category based on a sum

Hi there,   I am looking for a (hopefully) simple DAX measure to solve the following problem.   I have a range consisting of a number of employees, a chosen product, and their savings in each pro...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    These measures work

    Total amount = SUM(Data[Amount])

    Top product = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Product] ), [Total amount] ), 1 )

    Hope this helps.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

     

deevo's avatar
deevo
Icon for Helper I rankHelper I
3 years ago

Hi Ashish,

I have stumbled across this thread and it seems to be very similar to what I am trying to achieve, but I am unable to get the results with your measures.

 

Scenario:

  • I have 4 qualified Project managers working on a single project, and each person is required to do different hours of work.
  • Of the 4, I need to choose one project manager for this single project.
  • The rule to determine the project manager is to SUM(Required Effort), and the project manager with the highest totals hours will be assigned the role.
  • I need this person to be displayed across all rows for the same projectID (see screenshot of expected results).
  • Also, I need for the Lead Project Manager to always be attached to the unique ProjectID, if I need to use it in another table where I only want to display the ProjectID alongside the Lead Project Manager.

 

I would appreciate your help on this one, I am so close yet so far.

Regards

Denny

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

Hi,

These calculated column formulas work

Required effort per Manager for a ProjectID = CALCULATE(SUM(Data[Required Effort]),FILTER(Data,Data[ProjectID]=EARLIER(Data[ProjectID])&&Data[Project Manager]=EARLIER(Data[Project Manager])))
Max of accumulated required effort = CALCULATE(MAX(Data[Required effort per Manager for a ProjectID]),FILTER(Data,Data[ProjectID]=EARLIER(Data[ProjectID])))
Lead project manager = LOOKUPVALUE(Data[Project Manager],Data[Required effort per Manager for a ProjectID],Data[Max of accumulated required effort],Data[ProjectID],Data[ProjectID])

Hope this helps.

  • deevo's avatar
    deevo
    Icon for Helper I rankHelper I
    3 years ago

    This worked perfectly. Thank you so much. Your knowledge is amazing. 

    I will have to go back and break each formula down to understand how it works.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    You are welcome.  Thank you for your kind words.  If my previous reply helped, please mark that reply as Answer.