Sorting a Stacked Bar Chart with Switch Measures

[DHB]

I have a stacked bar chart which displays data using two different measures (Head Count and EFTSL) and I can flip from one to the other using a switch in a Chiclet slicer.

In the chart I use this measure in the X-axis;

EFTSL/HC = IF (

    HASONEVALUE ( 'SWITCH_Measures'[SWITCH Measures] ),

    SWITCH (

        FIRSTNONBLANK ( 'SWITCH_Measures'[SWITCH Measures], 1 ),

        "Head Count",

            DISTINCTCOUNT( 'F - Enrolment Fact'[Person Identifier] ),

        "EFTSL", SUM ( 'F - Enrolment Fact'[EFTSL] ),

        BLANK ()

    ),

    BLANK ()

)

Now the chart is supposed to sort the bars by the total value of each bar, which it will do fine if I don’t put a legend in, but I need to use a legend and then it gets a bit confused somewhere.

Do you have any suggestions as to anything I could try?

Thank you!

DHB

[amitchandak · Accepted solution]

@DHB , Have you tried Switch using field parameters? And check if that suites to your requirements better

[DHB]

In the end using Count instead of DistinctCount fixed it for me.  I think the distinct count was a problem once I threw a legend into the chart.  Without the legend either type of count was fine.  Thanks for the new method of switching - it's much easier than what I had been doing before.  I'll definitely be using that in future.