Forum Discussion

k1s2's avatar
k1s2
Icon for Helper I rankHelper I
2 years ago

Stacked bar y-axis based on another chart

I want to set the y-axis of a stacked bar chart to match that of another stacked bar chart.

 

In both charts the x-axis is by Month

 

The bars in Chart A are from 3 measures which count the words "High", "Medium" and "Low" the column [Original Risk] in table q_Compliance  

 

i.e. something like:

No. of High Original Risks =
CALCULATE(COUNTA(q_Compliance[Original Risk]), q_Compliance[Original Risk] - "High",
USERELATIONSHIP('Calendar'[Date],q_Compliance[Risk Assessment Date]))
...and the same for Medium & low... and then I have 3 more similar measures for Chart B except they use the column [Current Risk]

It looks as though a way to synchronise the y-axes could be to create a measure that calculates the maximum of the total number of each High + Medium + Low each month and then add say 10% and use that as the y-axis maximum range for both, but I'm stuck working out how to create that measure and would appreciate some help!

Need Help

5 Replies

  • k1s2's avatar
    k1s2
    Icon for Helper I rankHelper I
    2 years ago

    Apparently I can't attach a file. 

    Nor can I insert pictures or code without this [mindboggling un-user friendly] forum software stripping out what it declares to be inappropriate html...

     

    Here is a link to the file: https://drive.google.com/file/d/1r6eNKsoxmrnzqjjIUiQG-XLHAcRO0IBk/view?usp=sharing

     

    The data is in a table like the one shown in the attached picture.

      

    I'm using measures like this to provide counts each of "High, "Medium" & "Low"

     

    No. of High Original Risks = 
CALCULATE(
COUNTA(q_Compliance[Original Risk]), 
q_Compliance[Original Risk] = "High" 
, USERELATIONSHIP('Calendar'[Date],q_Compliance[Risk Assessment Date]) 
)

     

    Then using these in stacked bar visuals like in the other attached picture: 

     

    ...I’m trying to create a measure to select as the Maximum Range for the Y-Axis for both charts

     

    The measure should be logically something like

     

    110% times

       (the maximum, in any given period,
               (the maximum of the sum of the counts of the High, Medium & Low from the [Original Risk] column)
                and

               (the maximum of the sum of the counts of the High, Medium & Low from the [Current Risk] column)
      in that period)


     

    • Daniel29195's avatar
      Daniel29195
      Icon for Community Champion rankCommunity Champion
      2 years ago

      Hello k1s2 , 

      from what i understand, you want to set the max of the y-axis to be the same base on the max between the 2 meaures. ( first measure is based on the original risk , the second on the current risk ) for the 2 charts


      you can create the following measure : 

      max_y_axis = 
      var _original_risk_max = 

      maxx(
      allselected( table_name[original risk]),

      calculate( aggregation, userrelationship ( .... , ..... ) ) 

      )

       

      var _current_risk_max = 

      maxx(
      allselected( table_name[original risk]),

      calculate( aggregation , userrelationship ( .... , ..... ) ) 

      )

       

      RETURN 

       max ( _current_risk_max , _original_risk_max )

       

       

      hope this helps. 

      • j1s's avatar
        j1s
        Icon for Helper I rankHelper I
        2 years ago

        Thanks for your reply

         

        I think in  the second variable you meant allselected( table_name[current risk]), not allselected( table_name[original risk])?

         

        But what should I use as the aggregation in the Calculate?

        When I use COUNTA, like this...

         

         

        Max_y_axis = 
VAR _original_risk_max = 
MAXX(
    ALLSELECTED(q_Compliance[Original Risk]),
    CALCULATE( COUNTA(q_Compliance[Original Risk]), USERELATIONSHIP('Calendar'[Date],q_Compliance[Risk Assessment Date]) ) 
    )

VAR _current_risk_max = 
maxx(
    ALLSELECTED(q_Compliance[Current Risk]),
    CALCULATE( COUNTA(q_Compliance[Current Risk]) , USERELATIONSHIP('Calendar'[Date],q_Compliance[Risk Assessment Date]) ) 
    )

RETURN 
 MAX( _current_risk_max , _original_risk_max )

         

        ... I get an unexpected result:

         

         

        In this example , the measure says 26, but using it as the chart Y max, the Y axis max is 20.