Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
11 months ago
Solved

Difference when some Match and some do not

I have data like this:
Date Area ContinentSold
2025-12-28CentralAsia5
2025-12-28EastAsia6
2025-12-28WestAsia8
2025-12-28CentralAmerica4
2025-12-28EastAmerica4
2025-12-29CentralAsia7
2025-12-29EastAsia7
    
 
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
 
 AmericaAmericaAsiaAsiaAsia
 CentralEastCentralEastWest
America + CentralBlank0BlankBlankBlank
America + East0BlankBlankBlankBlank
Asia + CentralBlankBlank Blank-1-3
Asia + EastBlankBlank1 Blank-2
Asia + West Blank Blank32 Blank

Looking for the DAX to create it
DAX
  • VahidDM's avatar
    VahidDM
    11 months 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.

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    11 months 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.

  • GeraldGEmerick's avatar
    GeraldGEmerick
    Super User
    11 months ago

    Anonymous 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

     

     

     

  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    11 months ago

    Hi Anonymous 

     

    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.