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

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

Hi eyeball,

 

Based on my test, your modified formula should work in your scenario. 

 

Could you share a sample pbix file which can reproduce the issue? So that I could help further investigate on the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.:smileyhappy:

 

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?