6 Replies Latest reply on Oct 29, 2018 2:35 PM by luisa.bez

    Sort with Table Calcs and Parameters

    luisa.bez

      I've got a dashboard where I'd like the user to be able to sort descending by either ethnicity or gender. However, my measure is a percent of total table calculation, which is the same for both dimensions. I'm stuck on the logic part which is something along the lines of "IF [Sort by]="Gender" THEN [Percent of Total for Gender] ELSEIF [Sort by]="Ethnicity" THEN [Percent of Total for Ethnicity] END". That still leaves me with the problem that I can't use this field in my sort field because it's a table calc so the workaround is probably an LOD.

      Ethnicity and Gender are two different sheets, but they should be sorted at the same time.

       

      I appreciate your help!

        • 1. Re: Sort with Table Calcs and Parameters
          Joe Oppelt

          I think you should be able to do what you want.


          I'm taking a look now.

          • 2. Re: Sort with Table Calcs and Parameters
            Joe Oppelt

            First of all, I changed your table calcs to do TABLE(down).  I think that gives you the values you should get.  (See sheets 3 and 4 to see the raw data for year 2017.)

             

            Next I did LODs you'll need for Gender.  And I arrive at a % of total calc that is displayed on the bars (and tooltips show you the table calc values.)

             

            Do the same for Ethnicity.

             

            And then you can select a field to sort by.  (It would work like your [Dimension Calc] field.)

             

            But I don't see what you expect to see when sort ethnicity by Gender's values.


            See attached.

            • 3. Re: Sort with Table Calcs and Parameters
              Joe Oppelt

              Oh, wait.  I think I understand.  You would want to see the bars for both sheets with age groups 1,3,5 at the top and 2,4 below that (if you sort by Gender.)

               

              Hang on...

              • 4. Re: Sort with Table Calcs and Parameters
                Joe Oppelt

                You're going to have to make a way for both sheets to recognize the value you "keep" on each sheet.  (You have "F" on the Gender sheet, and "BAME" on the ethnicity sheet.)

                 

                Perhaps use parameters to let the user select those.


                If you want the ethnicity sheet to be sorted by the F values on the Gender sheet, then you need to pick out the %Total values for F on the Ethnicity sheet.  So that's something you'll need to figure out based on the way you expect those values to selected for the respective sheets.


                But once you decide on that, you can pick out the "F" values for each [Dimension Calc] value using LOD at the level of [Dimension value].  (And I'm also not sure how you really want [Year] to come into play for all this.  For now I added [Year] to the initial LODs because you have it filtered at that level.  Will you always be selecting just one year at a time?  If not, we need to do something different.)

                • 5. Re: Sort with Table Calcs and Parameters
                  luisa.bez

                  Hi Joe, thanks for your help. Yes, I'll always be selecting just one year at a time. I should probably change it to MAX(Year0 though rather than hard-coding the filter. I'll look through your solution tomorrow!

                  • 6. Re: Sort with Table Calcs and Parameters
                    luisa.bez

                    OK, I got it to work. It was so simple in the end. Not sure why I got stuck on the LOD so thanks for your help. I do want it to be table across though because I'd like to see the gender/ethnicity split for each age group.