Totals are not correct

[MichaelSamiotis]

Hello all,

I have the below but as you can guess my totals don't agree as my matrix is per shop and per year.

VAR PreviousYearDates = PREVIOUSYEAR ( 'Dim Dates'[Date] )

VAR calc =

CALCULATE (

    DISTINCTCOUNT( 'Fact Sales'[customer_id] ),

    LEFT ( 'Fact Sales'[Shop_Code],1 ) = "S",

    PreviousYearDates,

    KEEPFILTERS (

        NOT (

            ISBLANK ( 'Fact Sales'[Shop] )

        )

    )

)

I have tried the below but doesn't work:

VAR summarisedtable=

    SUMMARIZE('Fact Sales','Fact Sales'[Shop],'Dim Dates'[Year],"Distinct customers per year",calc)

RETURN

IF ( 
    HASONEVALUE(

        'Dim Shop'[Shop]) && HASONEVALUE('Dim Dates'[Year]),

calc,

SUMX(summarisedtable,[Distinct customers per year])

)

Can you please help with that? Thank you all!

[MichaelSamiotis · Accepted solution]

VAR PreviousYearDates = PREVIOUSYEAR ( 'Dim Dates'[Date] )

VAR calc =
CALCULATE (
    DISTINCTCOUNT( 'Fact Sales'[customer_id] ),
    LEFT ( 'Fact Sales'[Shop_Code],1 ) = "S",
    PreviousYearDates,
    KEEPFILTERS (
        NOT (
            ISBLANK ( 'Fact Sales'[Shop] )
        )
    )
)

VAR TotalCalc =
CALCULATE (
    DISTINCTCOUNT( 'Fact Sales'[customer_id] ),
    LEFT ( 'Fact Sales'[Shop_Code],1 ) = "S",
    PreviousYearDates
)

RETURN
IF ( 
    HASONEVALUE('Dim Shop'[Shop]) && HASONEVALUE('Dim Dates'[Year]),
    calc,
    TotalCalc
)

[MichaelSamiotis]

I tried the above with sumx but doesn't work.

[ppm1]

Please see this article for how to get your expected totals.

(2) Power BI - Tales from the front #01 - Getting the Right Total - YouTube

Pat