SUMX totals

[Anonymous]

Hello everyone, 

This may have been asked numerous times before, but I still struggled get to the correct total with SUMX despite going through the many examples posted here. 

I have a measure "Debit LY" which calls the last-year-same-weekday number for stores. However, the total (548,545) is not adding up as what would have been expected like the table on the right (911,377). Below is the measure that I wrote, any help is greatly appreciated !

Link to the file 

Debit LY = 
IF (
    COUNTROWS( 'Date' ) = 1,
    CALCULATE (
        SUM ( Debit[Debit] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Full Date] = SELECTEDVALUE ( 'Date'[LY Day2Day] ) )
    ),
    SUMX (
        SUMMARIZE (
            ALL ( Debit ),
            'Date'[Full Date],
            Store[Store],
            Debit[Debit]
        ),
        CALCULATE (
            SUM ( Debit[Debit] ),
            FILTER (  ALL ( 'Date' ) , 'Date'[Full Date] = 'Date'[LY Day2Day] ),
            ALL ( Store[Store] )
        )
    )
)

[AlB]

Hi @Anonymous 

Try this measure that uses the one you have:

Debit LY TOT =
SUMX (
    CROSSJOIN ( DISTINCT ( 'Date'[Full Date] ), DISTINCT ( Store[Store Code] ) ),
    [Debit LY]
)

Probably you can modify the original one as well. I haven't looked into it

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[ERD]

Hello @Anonymous ,

If I've understood you correctly, here is your measure:

DebitLY =
CALCULATE (
    SUM ( Debit[Debit] ),
    SAMEPERIODLASTYEAR ( DATEADD ( 'Date'[Full Date], 1, DAY ) )
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[amitchandak]

@Anonymous , Yet to check the file.

Try like

Debit LY = 
SUMX (
        ADDCOLUMNS(SUMMARIZE (
            ALL ( Debit ),
            'Date'[Full Date],
            Store[Store],
            Debit[Debit]),
        "_1", 

IF (
    COUNTROWS( 'Date' ) = 1,
    CALCULATE (
        SUM ( Debit[Debit] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Full Date] = SELECTEDVALUE ( 'Date'[LY Day2Day] ) )
    ),
    
        CALCULATE (
            SUM ( Debit[Debit] ),
            FILTER (  ALL ( 'Date' ) , 'Date'[Full Date] = 'Date'[LY Day2Day] ),
            ALL ( Store[Store] )
        )
    )
) ,[_1])

[Anonymous]

Thanks @amitchandak, but the above still returns the same result.