Add Total to clustered column chart - sorting issue

[Winniethewinner]

Hello everyone, 

I'm trying to add a total bar to the clustered column chart. 

Here is my Fact Table:

Here is the Channel Table created and I set up the relationship between the two tables using Channel: 

And below is the YoY% measure I created and trying to add to the visual: 

YoY % = IF(SELECTEDVALUE('Channel Table'[Channel])="TOTAL",CALCULATE(DIVIDE(SUM(Fact_Table[TOTAL CY])-SUM(Fact_Table[TOTAL LY]),SUM(Fact_Table[TOTAL LY])),ALL('Channel Table'[Channel])),DIVIDE(SUM(Fact_Table[TOTAL CY])-SUM(Fact_Table[TOTAL LY]),SUM(Fact_Table[TOTAL LY])))

However, I'd like the TOTAL to be displayed as the last column, and when I sort the Channel by Order, the TOTAL disappear from the visual: 

I'm not sure if it's a bug or I miss anything. Any suggestions would be appreciated. Thanks. 

[kpost · Accepted solution]

 

Not sure what I did differently but perhaps you can reverse-engineer the .pbix file I attached, I simply re-created your tables and solved the problem from the bottom up.

Here's the measure I ended up using.

YoY % =

VAR SELECTED_VAL = SELECTEDVALUE('Channel Dimension'[Channel])

VAR CY = COALESCE(SUM('Fact'[TOTAL CY]), 0)

VAR LY = COALESCE(SUM(Fact[TOTAL LY]), 0)

VAR YOY = DIVIDE(CY - LY, LY, 0)

VAR YOY_TOT = CALCULATE(DIVIDE(

            COALESCE(SUM('Fact'[TOTAL CY]), 0) - COALESCE(SUM(Fact[TOTAL LY]), 0),

            COALESCE(SUM(Fact[TOTAL LY]), 0),

            0), ALL('Channel Dimension'))

RETURN

IF (selected_val ="TOTAL", YOY_TOT, YOY)

[Winniethewinner]

Brilliant, it works! 

btw I also found I should change my formula to:
YoY % = IF(SELECTEDVALUE('Channel Table'[Channel])="TOTAL",CALCULATE(DIVIDE(SUM(Fact_Table[TOTAL CY])-SUM(Fact_Table[TOTAL LY]),SUM(Fact_Table[TOTAL LY])),ALL('Channel Table'[Channel])),DIVIDE(SUM(Fact_Table[TOTAL CY])-SUM(Fact_Table[TOTAL LY]),SUM(Fact_Table[TOTAL LY])))

and then it works too. 

Thanks for your help!