Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |