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 betwee...
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

     

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