Forum Discussion

eyeball's avatar
eyeball
Frequent Visitor
9 years ago
Solved

Sort by another column error

Hi,   I've created a cluster chart to display companies and the number of activities that our sales force have logged against them (Emails, calls etc).  I would like to sort the data by the pipelin...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi eyeball,

     

    My mistake! Please try the formula below.:smileylol:

    MaxPipeline =
VAR currentCompany = 'Table1'[company]
RETURN
    CALCULATE (
        MAX ( 'Table1'[pipeline] ),
        FILTER ( ALL ( Table1 ), 'Table1'[company] = currentCompany )
    )

     

    Regards

eyeball's avatar
eyeball
Frequent Visitor
9 years ago

Hi v-ljerr-msft

 

I've been doing a bit of investigation and I think I may have found why I get the error.  I created a calculated table and populated it with a ditinct list of companies and the summed pipeline value for each.  I then created a chart of all the companies and displayed then pipline value.  I have instances where company names are identicle, even though technically they are considered different.  To use an example, I could have a branch of ABC Ltd in London and a branch of ABC Ltd in New York.  I believe that when I try to sort against the name column it throws the error because is see them as dupes and doesn't know which pipeline value to use.

 

If I changed the chart to use companyId which is a truly unique Id and then sort pipeline against that, it all seems to work!

 

Does that sound correct?

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi eyeball,

 

Yes, the solution requires the company names to be unique to work correctly. If not, using companyId which is truly unique is needed.:smileyhappy:

 

Regards

  • eyeball's avatar
    eyeball
    Frequent Visitor
    9 years ago

    Hi v-ljerr-msft

     

    In that case, your solution works perfectly!  Thanks very much for all the help :smileyhappy: