7 Replies Latest reply on Nov 29, 2017 4:12 AM by Simon Jacobs

Sync sort order across worksheets

Hi,

I have four worksheets that provide keys stats on brands (awareness, familiarity,appeal and conversion).  I then display these four charts in a dashboard.  I'd like to be able to sort on awareness in descending order and then have the other three sheets sorted in the same order as the awareness chart.  I've uploaded a sample workbook.

Simon

• 1. Re: Sync sort order across worksheets

Try this blog.

Pic copied from below blog.

1 of 1 people found this helpful
• 2. Re: Sync sort order across worksheets

Hi Simon,

Create a parameter.

Create a calculated field.

Right click "Brand" > select sort>Field>Sort

Display parameter control.

Hope this helps.

Regards,

Tushar

• 3. Re: Sync sort order across worksheets

Thank you for the help Tushar.  Unfortunately this doesn't quite achieve what I need to do.  This solution sorts each dimension within itself.  What I want to achieve is all charts sorted based on the Awareness dimension.

E.g.

Brand 1 has awareness of 80% and appeal of 20%

Brand 2 has awareness of 60% and appeal of 40%

In the proposed solution if I sort in descending order Brand 1 appears 1st on the Awareness chart and Brand 2 appears 1st on the appeal chart.  What I'm trying to achieve is that the awareness chart is sorted in descending order so Brand 1 appears 1st, Brand 2 2nd.  Then on the appeal chart the brands are also sorted in descending order based on awareness so Brand 1 still appears 1st even though appeal is higher for Brand 2.

E.g.:

Thanks,

Simon

• 4. Re: Sync sort order across worksheets

Hi Simon,

I could see that currently you are using Question field to get different views.

Now in order to create similar ranking across all charts. You need to get rid of filter and create calculated measures for all charts.

Measure for Aware, Measure for Familiarity etc.

One you have all chart based on such custom measures.

You can create ranking based on

RANK_UNIQUE(sum(if [Question]='BrandAware - SUMMARY1'

then [Value]

end),'desc')

You can use this ranking in all charts before brand and hide the headers.

You can also give it a try using index().

Key is to make all charts based on custom measures.

1 of 1 people found this helpful
• 5. Re: Sync sort order across worksheets

Thanks Amit, this works really well.  My charts are all now sorting correctly. The only problem I have is now some of my other calculations based on LODs are not working and I'm not sure how to fix them.  Previously I had a tool tip based an LOD to pull through the corresponding value from the 2016 survey:

{ FIXED [Country],[Question],[Output+ (Multiple Connections)].[Brand],[Measure],[Data Cut] : sum(IF [Wave] = "2016" THEN [Value] END)}

Since I no longer have question as a filter and am not using the ‘measure’ field due to creating separate measures for awareness, familiarity and appeal I have tried to revise this approach by creating corresponding 2016 measures.

1. e.g.

{ FIXED [Country],[Brand],[Data Cut] : sum(IF [Wave] = "2016" AND [Measure] = "Aware" THEN [Value] END)}

Unfortunately this isn’t working correctly.  From what I can tell it’s summing the 2016 awareness figure across all brands (the 1544% shown below is the updated measure) rather than splitting it out by brand which I would expect since I’ve included ‘Brand’ in the LOD and I have ‘Brand’ on the rows shelf.

Are you able to advise on how to fix this?

Thanks,

Simon

• 6. Re: Sync sort order across worksheets

Could you send your latest workbook?

It would be easy to have a look at it.

• 7. Re: Sync sort order across worksheets

Thanks Amit.  I just managed to fix it this morning.

I took out the reference ‘[Measure]’ in the ‘if’ statement and replaced [Value] with the specific ‘Aware’ measure I’d created when implementing your solution.

{ FIXED [Country],[Data Cut],[Brand]:SUM(IF [Wave]="2017" THEN [.Aware] END)}

- { FIXED [Country],[Data Cut],[Brand]:SUM(IF [Wave]="2016" THEN [.Aware] END)}

The dashboard now works perfectly. Thank you for your help with this.

Simon