Difficulty with INSCOPE to display the total result

[4lanR]

Hello,

I am having difficulty creating a DAX formula that returns the sum of the differences for each customer within a group. For example, I used the ISINSCOPE formula to perform the calculation with each customer code within the industry segment, but I would like to see the total sum of these differences, which would be 5.49 and not 1.10 in +Δ. With this, I can build an analysis of customer consumption behaviors compared to the same period last year.

here is the code I am using:

+Δ =

VAR

SalesPriorYear = IF(

ISINSCOPE(Sales[Site_Code]),

CALCULATE(

       SUM(Sales[Amount]),

PARALLELPERIOD(

        calendar[Date],

               -12,

               MONTH

  )

       ),

         CALCULATE(

        SUM(Sales[Amount]),

        PARALLELPERIOD(

                calendar[Date],

         -12,

         MONTH

         )

              )

)

RETURN

IF(

AND(

(SUM(Sales[Amount]) - SalesPriorYear) > 0,

SalesPriorYear <> BLANK()

),

SUM(Sales[Amount]) - SalesPriorYear

)

[4lanR]

Thank you @parry2k, I managed to solve the problem with your solution. @_AAndrade , your solution also worked, but the summation still considered the differences when there was blank consumption. Nevertheless, thank you very much for the support, everyone 😁

[parry2k · Accepted solution]

@4lanR @_AAndrade has given a great solution but not to keep the logic at the multiple places this is what I will do. Just a suggestion.

Sum Sales = SUM ( Sales[Amount] )

LY Sales = CALCULATE ( [Sum Sales], SAMEPERIODLASTYEAR ('calendar'[Date] ) )

+ve Diff = 
VAR __Diff = [Sum Sales] - COALESCE ( [LY Sales], 0 )
RETURN IF ( __Diff > 0, __Diff )

+Δ = 
IF(
 ISINSCOPE(Sales[Site_Code]),
    [+ve Diff],
    SUMX(
        SUMMARIZE(
        Sales,
        Sales[Segment],
        Sales[Site_Code]
        ),
        [+ve Diff]
    )
)

[_AAndrade]

Hi @4lanR,

I don't understand the first part of your dax code becaus both part of the IF statments seems to me equal.

But using you measure, please try this:

+Δ =
VAR
SalesPriorYear = IF(
ISINSCOPE(Sales[Site_Code]),
CALCULATE(
       SUM(Sales[Amount]),
PARALLELPERIOD(
        calendar[Date],
               -12,
               MONTH
  )
       ),
         CALCULATE(
        SUM(Sales[Amount]),
        PARALLELPERIOD(
                calendar[Date],
         -12,
         MONTH
         )
              )
)
VAR DifMeasure =

IF(
AND(
(SUM(Sales[Amount]) - SalesPriorYear) > 0,
SalesPriorYear <> BLANK()
),
SUM(Sales[Amount]) - SalesPriorYear
)

VAR TotalTab = 
ADDCOLUMNS(
  SUMMARIZE(
           Sales,
           Sales[Segment] 
           ),
  "@Total", DifMeasure 
)
RETURN
IF(
 ISINSCOPE(Sales[Site_Code]),
 Dif,
 SUMX(TotalTab, [@Total])
)

[4lanR]

Thanks. I tried using this solution, but the expected result was the same; I still couldn't sum all the differences 😢

[_AAndrade]

Could you share a pbix with same data so I can take look?

[4lanR]

Sample.pbix

[_AAndrade]

Hi @4lanR,

I'm attaching a pbix file with my solution.