Forum Discussion
Difference when some Match and some do not
- 1 year ago
Use a 2-dimension (row/column) setup and one measure.
1) Create a location dim and a copy (for columns)
DimLoc =
DISTINCT ( SELECTCOLUMNS ( Fact, "Continent", Fact[Continent], "Area", Fact[Area] ) )DimLoc_Col = DimLoc
Relate Fact[Continent],[Area] → DimLoc[...] and Fact[...] → DimLoc_Col[...] (both many-to-one, active). Keep your Date table slicer linked to Fact.2) Measure for the matrix
Put DimLoc on Rows and DimLoc_Col on Columns, then use:Diff :=
VAR sameContinent =
SELECTEDVALUE(DimLoc[Continent]) = SELECTEDVALUE(DimLoc_Col[Continent])
VAR sameArea =
SELECTEDVALUE(DimLoc[Area]) = SELECTEDVALUE(DimLoc_Col[Area])
RETURN
IF (
NOT sameContinent, BLANK(),
IF (
sameArea, BLANK(),
VAR rowSold =
CALCULATE ( SUM ( Fact[Sold] ), ALL ( DimLoc_Col ) ) -- only row loc
VAR colSold =
CALCULATE ( SUM ( Fact[Sold] ), ALL ( DimLoc ) ) -- only column loc
RETURN rowSold - colSold
)
)
This returns blank when continents differ or areas match; otherwise it shows the difference for the selected date.
Use a 2-dimension (row/column) setup and one measure.
1) Create a location dim and a copy (for columns)
DimLoc =
DISTINCT ( SELECTCOLUMNS ( Fact, "Continent", Fact[Continent], "Area", Fact[Area] ) )
DimLoc_Col = DimLoc
Relate Fact[Continent],[Area] → DimLoc[...] and Fact[...] → DimLoc_Col[...] (both many-to-one, active). Keep your Date table slicer linked to Fact.
2) Measure for the matrix
Put DimLoc on Rows and DimLoc_Col on Columns, then use:
Diff :=
VAR sameContinent =
SELECTEDVALUE(DimLoc[Continent]) = SELECTEDVALUE(DimLoc_Col[Continent])
VAR sameArea =
SELECTEDVALUE(DimLoc[Area]) = SELECTEDVALUE(DimLoc_Col[Area])
RETURN
IF (
NOT sameContinent, BLANK(),
IF (
sameArea, BLANK(),
VAR rowSold =
CALCULATE ( SUM ( Fact[Sold] ), ALL ( DimLoc_Col ) ) -- only row loc
VAR colSold =
CALCULATE ( SUM ( Fact[Sold] ), ALL ( DimLoc ) ) -- only column loc
RETURN rowSold - colSold
)
)
This returns blank when continents differ or areas match; otherwise it shows the difference for the selected date.