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 ta...
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)
     
     
     
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!