Forum Discussion

weinmayr's avatar
weinmayr
Regular Visitor
7 years ago
Solved

Merge charts

I have two charts generated from different slicers on one page, both using the same data source.  I want to create a third chart in which the slicers don't apply, and that shows the difference between the other two chart values.  For example, below we have four different slicers per chart (left and right), and a third chart in the bottom/middle.  I want the bottom/middle chart to show a row called "Arts and Performing Arts" with a value of 4 (derived from 28 on the left chart minus 24 on the right chart).

 

Thanks.

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi weinmayr ,

     

    In that case, you should create two separate slicers from two unrelated tables, which are also unlinked to fact data table.

     

    SlicerTable1 = SUMMARIZE(Sheet2,Sheet2[City],Sheet2[Province])
SlicerTable2 = SUMMARIZE(Sheet2,Sheet2[City],Sheet2[Province])

     

     

    Then, create measures.

     

    Amount1 =
VAR isfilterCity =
    ISFILTERED ( SlicerTable1[City] )
VAR isfilterProvince =
    ISFILTERED ( SlicerTable1[Province] )
VAR selectedCity =
    SELECTEDVALUE ( SlicerTable1[City] )
VAR selectedProvince =
    SELECTEDVALUE ( SlicerTable1[Province] )
RETURN
    IF (
        isfilterCity = TRUE (),
        IF (
            isfilterProvince = TRUE (),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER (
                    ALLEXCEPT ( Sheet2, Sheet2[Name] ),
                    Sheet2[City] = selectedCity
                        && Sheet2[Province] = selectedProvince
                )
            ),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER ( ALLEXCEPT ( Sheet2, Sheet2[Name] ), Sheet2[City] = selectedCity )
            )
        ),
        IF (
            isfilterProvince = TRUE (),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER (
                    ALLEXCEPT ( Sheet2, Sheet2[Name] ),
                    Sheet2[Province] = selectedProvince
                )
            ),
            SUM ( Sheet2[Amount] )
        )
    )

Amount2 =
VAR isfilterCity =
    ISFILTERED ( SlicerTable2[City] )
VAR isfilterProvince =
    ISFILTERED ( SlicerTable2[Province] )
VAR selectedCity =
    SELECTEDVALUE ( SlicerTable2[City] )
VAR selectedProvince =
    SELECTEDVALUE ( SlicerTable2[Province] )
RETURN
    IF (
        isfilterCity = TRUE (),
        IF (
            isfilterProvince = TRUE (),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER (
                    ALLEXCEPT ( Sheet2, Sheet2[Name] ),
                    Sheet2[City] = selectedCity
                        && Sheet2[Province] = selectedProvince
                )
            ),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER ( ALLEXCEPT ( Sheet2, Sheet2[Name] ), Sheet2[City] = selectedCity )
            )
        ),
        IF (
            isfilterProvince = TRUE (),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER (
                    ALLEXCEPT ( Sheet2, Sheet2[Name] ),
                    Sheet2[Province] = selectedProvince
                )
            ),
            SUM ( Sheet2[Amount] )
        )
    )

    Amount Diff = [Amount2]-[Amount1]

     

    Best regards,

    Yuliana Gu

     

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi weinmayr ,

     

    In that case, you should create two separate slicers from two unrelated tables, which are also unlinked to fact data table.

     

    SlicerTable1 = SUMMARIZE(Sheet2,Sheet2[City],Sheet2[Province])
SlicerTable2 = SUMMARIZE(Sheet2,Sheet2[City],Sheet2[Province])

     

     

    Then, create measures.

     

    Amount1 =
VAR isfilterCity =
    ISFILTERED ( SlicerTable1[City] )
VAR isfilterProvince =
    ISFILTERED ( SlicerTable1[Province] )
VAR selectedCity =
    SELECTEDVALUE ( SlicerTable1[City] )
VAR selectedProvince =
    SELECTEDVALUE ( SlicerTable1[Province] )
RETURN
    IF (
        isfilterCity = TRUE (),
        IF (
            isfilterProvince = TRUE (),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER (
                    ALLEXCEPT ( Sheet2, Sheet2[Name] ),
                    Sheet2[City] = selectedCity
                        && Sheet2[Province] = selectedProvince
                )
            ),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER ( ALLEXCEPT ( Sheet2, Sheet2[Name] ), Sheet2[City] = selectedCity )
            )
        ),
        IF (
            isfilterProvince = TRUE (),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER (
                    ALLEXCEPT ( Sheet2, Sheet2[Name] ),
                    Sheet2[Province] = selectedProvince
                )
            ),
            SUM ( Sheet2[Amount] )
        )
    )

Amount2 =
VAR isfilterCity =
    ISFILTERED ( SlicerTable2[City] )
VAR isfilterProvince =
    ISFILTERED ( SlicerTable2[Province] )
VAR selectedCity =
    SELECTEDVALUE ( SlicerTable2[City] )
VAR selectedProvince =
    SELECTEDVALUE ( SlicerTable2[Province] )
RETURN
    IF (
        isfilterCity = TRUE (),
        IF (
            isfilterProvince = TRUE (),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER (
                    ALLEXCEPT ( Sheet2, Sheet2[Name] ),
                    Sheet2[City] = selectedCity
                        && Sheet2[Province] = selectedProvince
                )
            ),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER ( ALLEXCEPT ( Sheet2, Sheet2[Name] ), Sheet2[City] = selectedCity )
            )
        ),
        IF (
            isfilterProvince = TRUE (),
            CALCULATE (
                SUM ( Sheet2[Amount] ),
                FILTER (
                    ALLEXCEPT ( Sheet2, Sheet2[Name] ),
                    Sheet2[Province] = selectedProvince
                )
            ),
            SUM ( Sheet2[Amount] )
        )
    )

    Amount Diff = [Amount2]-[Amount1]

     

    Best regards,

    Yuliana Gu