Difference when some Match and some do not

[FPP]

I have data like this:

Date	Area	Continent	Sold
2025-12-28	Central	Asia	5
2025-12-28	East	Asia	6
2025-12-28	West	Asia	8
2025-12-28	Central	America	4
2025-12-28	East	America	4
2025-12-29	Central	Asia	7
2025-12-29	East	Asia	7

The user chooses a date via a filter

I want to show a difference via these rules:

IF Continent does not match show Blank
IF Continent does match and Area does match show Blank
IF Continent does match and Area does not match show the difference between the Sold values

Example:

Asia Central compared to Asia Central is Blank (since Continent  and Area matches)

Asia Central compared to Asia East is 5 - 6 = -1

Asia Central compared to Asia West is 5 - 8 = -3

Asia Central compared to America Central is Blank (since Continent does not match)

Asia Central compared to America West is Blank (since Continent does not match)

I want to use a matrix report to display them:

User choice is 2025-12-28

	America	America	Asia	Asia	Asia
	Central	East	Central	East	West
America + Central	Blank	0	Blank	Blank	Blank
America + East	0	Blank	Blank	Blank	Blank
Asia + Central	Blank	Blank	Blank	-1	-3
Asia + East	Blank	Blank	1	Blank	-2
Asia + West	Blank	Blank	3	2	Blank

Looking for the DAX to create it

[Selva-Salimi]

Hi @FPP 

create a table (*this table shouldn't have any relation with your data table*) :

Table 2 = SUMMARIZE('Data table','Data table'[ Continent],'Data table'[ Area])

then write a measure:

Measure = var rs = calculate(SUM('Data table'[Sold]), 'Data table'[ Area]=SELECTEDVALUE('Table 2'[ Area]) && 'Data table'[ Continent]=SELECTEDVALUE('Table 2'[ Continent]))

 RETURN if(SELECTEDVALUE('Data table'[ Continent]) <> SELECTEDVALUE('Table 2'[ Continent]) || (SELECTEDVALUE('Data table'[ Area])=SELECTEDVALUE('Table 2'[ Area]))  ,blank(), rs-SUM('Data table'[Sold]))

Use a Matrix visual:

     Rows → from Table2

     Columns → from your Data table

     Values → the measure you created

Turn off row and column subtotals

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[VahidDM · Accepted solution]

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.

[GeraldGEmerick]

@FPP You will need to create an urelated table like the following:

Table 2 = SELECTCOLUMNS( 'Table', "Continent", [Continent], "Area", [Area] )

Use this as either the rows or columns in the matrix and use the Country and Area from the original table as the other (row/column). You can then use a measure like the following:

Measure = 
    VAR _Continent1 = MAX( 'Table'[Continent] )
    VAR _Area1 = MAX( 'Table'[Area] )
    VAR _Continent2 = MAX( 'Table 2'[Continent] )
    VAR _Area2 = MAX( 'Table 2'[Area] )
    VAR _Date = MAX( 'Table'[Date] )
    VAR _Return =
        SWITCH( TRUE(),
            _Continent1 <> _Continent2, BLANK(),
            _Area1 = _Area2, BLANK(),
            SUM( 'Table'[Sold] ) - CALCULATE( SUM( 'Table'[Sold] ), FILTER( ALL( 'Table'), 'Table'[Date] = _Date && 'Table'[Continent] = _Continent2 && 'Table'[Area] = _Area2 ) )
        )
RETURN
    _Return