Forum Discussion

Winniethewinner's avatar
Winniethewinner
Helper IV
2 years ago
Solved

Add Total to clustered column chart - sorting issue

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. 

Need Help
  • kpost's avatar
    kpost
    2 years ago

     

    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)
     
     
     

2 Replies

  • kpost's avatar
    kpost
    Solution Sage
    2 years ago

     

    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's avatar
      Winniethewinner
      Helper IV
      2 years ago

      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!