Forum Discussion

ireneapplebe's avatar
ireneapplebe
Frequent Visitor
8 years ago
Solved

add difference for cluster bar chart

Spoiler
 

Hi there,

I have following data set and would like to display difference of 2016_W43 and 2017_W43 by A,B,C on the cluster chart.
A,B,C on x-axis and 2016_W43 and 2017_W43 as a legend. I want to see difference between 2016_W43 and 2017_W43 for A, for an example, Is this possible? If yes, how can I achieve this?

 

Data set below: 

 

2016_W43A4
2016_W43B3
2016_W43C2
2017_W43A3
2017_W43C2
2017_W43C1
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi ireneapplebe,

     

    Based on my test, you should be able to follow steps below to get your expected result.

     

    I assume you have a table called Table1 like below.

     

     

    1. Add a new table called Table2 like below, and mark sure there is a relationship between Table1 and Table2.

     

     

    2. Use the formula below to create a measure.

    Measure = 
IF (
    HASONEVALUE ( Table2[Column 1] ),
    IF (
        VALUES ( Table2[Column 1] ) = "Diff",
        CALCULATE (
            SUM ( Table1[Column 3] ),
            FILTER ( ALL ( Table2 ), Table2[Column 1] = "2016_W43" )
        )
            - CALCULATE (
                SUM ( Table1[Column 3] ),
                FILTER ( ALL ( Table2 ), Table2[Column 1] = "2017_W43" )
            ),
        SUM ( Table1[Column 3] )
    )
)

    3. Then show Table1[Column 2] as Axis, Table2[Column 1] Legend, and the [Measure] as Value on the cluster bar chart.

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi ireneapplebe,

     

    Based on my test, you should be able to follow steps below to get your expected result.

     

    I assume you have a table called Table1 like below.

     

     

    1. Add a new table called Table2 like below, and mark sure there is a relationship between Table1 and Table2.

     

     

    2. Use the formula below to create a measure.

    Measure = 
IF (
    HASONEVALUE ( Table2[Column 1] ),
    IF (
        VALUES ( Table2[Column 1] ) = "Diff",
        CALCULATE (
            SUM ( Table1[Column 3] ),
            FILTER ( ALL ( Table2 ), Table2[Column 1] = "2016_W43" )
        )
            - CALCULATE (
                SUM ( Table1[Column 3] ),
                FILTER ( ALL ( Table2 ), Table2[Column 1] = "2017_W43" )
            ),
        SUM ( Table1[Column 3] )
    )
)

    3. Then show Table1[Column 2] as Axis, Table2[Column 1] Legend, and the [Measure] as Value on the cluster bar chart.

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards