Forum Discussion

khsingh's avatar
khsingh
Regular Visitor
2 years ago

Stacked Chart - how to split Y axis value based on X axis but consider total value to divide

Hi,   in stacked chat i have used four columns on Y axis and two columns on X Axis and its connecetd to SSAS Tabular. the Y axis bars gets Splitted based on X axis but  i want avoid one of the c...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi khsingh 

 

Maybe you can using the Stacked column chart instead of 100% Stacked column chart:

Here I create a set of sample:

Then add 2 measures to calculate the percentage, it is important to note that the denominator is the sum of all the data, not the sum of the individual columns:

 

MEASURE =
SUM ( 'Table'[Values] )
    / (
        SUMX ( ALLSELECTED ( 'Table' ), 'Table'[Values] )
            + SUMX ( ALLSELECTED ( 'Table' ), 'Table'[Values2] )
    )

MEASURE 2 =
SUM ( 'Table'[Values2] )
    / (
        SUMX ( ALLSELECTED ( 'Table' ), 'Table'[Values] )
            + SUMX ( ALLSELECTED ( 'Table' ), 'Table'[Values2] )
    )

 

The result is as follow:

 

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

khsingh's avatar
khsingh
Regular Visitor
2 years ago

Hi,

 

Thanks for your message.

 

in this data, if you add date field and consider date field in X Axis.

Now data will split date wise.

my consern was, now calculation percentage shoud be total of date and divide  two measures 
Please check below picture 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi khsingh 

     

    Please try this:

    MEASURE =
VAR _currentdate =
    MAX ( 'Table'[Date] )
RETURN
    SUM ( 'Table'[Values] )
        / (
            SUMX (
                FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentdate ),
                'Table'[Values]
            )
                + SUMX (
                    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentdate ),
                    'Table'[Values2]
                )
        )

    MEASURE 2 =
VAR _currentdate =
    MAX ( 'Table'[Date] )
RETURN
    SUM ( 'Table'[Values2] )
        / (
            SUMX (
                FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentdate ),
                'Table'[Values]
            )
                + SUMX (
                    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentdate ),
                    'Table'[Values2]
                )
        )

    The result is as follow:

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • khsingh's avatar
      khsingh
      Regular Visitor
      2 years ago

      Hi,

       

      Since we connected to tabular model , below field is not working.

       

      Error : canccot find Name

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi khsingh 

         

        The measure is just a sample for your reference ,you need to change it to your own field, or you can not make it run.

         

        Best Regards

        Zhengdong Xu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.