cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Issue with SUMX in ISINSCOPE

Hello,

 

I have a measure that calculates reactivated users by date, and this produces the expected result.

However, I am not able to produce the sum of reactivated users for all days within the selected month.

 

When I manually add up all reactivated users for a month, it does not match with the result produced on monthly level by the measure. 

 

Does anyone have an idea why the monthly total is producing a different result from adding the daily numbers up manually?

 

Regards,

Adrian

 

Reactivated = 

VAR __CurrentUsers =
    CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Order'[IsCompleted] = "Yes" )

VAR __UsersLast180Days =
    CALCULATETABLE (
        VALUES ( 'F Orders'[UserId] ),
        FILTER (
            ALL ( 'D Calendar'[Date] ),
            'D Calendar'[Date]
                >= MIN ( 'D Calendar'[Date] ) - 180
                && 'D Calendar'[Date]
                    <= MIN ( 'D Calendar'[Date] ) - 1
        ),
        'D Order'[IsCompleted] = "Yes"
    )

VAR __UserBefore180Days =
    CALCULATETABLE (
        VALUES ( 'F Orders'[UserId] ),
        FILTER (
            ALL ( 'D Calendar'[Date] ),
            'D Calendar'[Date]
                < MIN ( 'D Calendar'[Date] ) - 180
        ),
        'D Order'[IsCompleted] = "Yes"
    )

VAR __Result =
    CALCULATE (
        DISTINCTCOUNT ( 'F Orders'[UserId] ),
        FILTER (
            ALL ( 'F Orders'[UserId] ),
            'F Orders'[UserId] IN __CurrentUsers
                && 'F Orders'[UserId] IN __UserBefore180Days
                && NOT 'F Orders'[UserId] IN __UsersLast180Days
        )
    )

RETURN
    IF (
        ISINSCOPE ( 'D Calendar'[Date] ),
        __Result,
        SUMX (
            VALUES ( 'D Calendar'[Date] ),
            CALCULATE (
                CALCULATE (
                    DISTINCTCOUNT ( 'F Orders'[UserId] ),
                    FILTER (
                        ALL ( 'F Orders'[UserId] ),
                        'F Orders'[UserId]
                            IN CALCULATETABLE ( VALUES ( 'F Orders'[UserId] ), 'D Order'[IsCompleted] = "Yes" )
                            && 'F Orders'[UserId]
                                IN CALCULATETABLE (
                                    VALUES ( 'F Orders'[UserId] ),
                                    FILTER (
                                        ALL ( 'D Calendar'[Date] ),
                                        'D Calendar'[Date]
                                            < MIN ( 'D Calendar'[Date] ) - 180
                                    ),
                                    'D Order'[IsCompleted] = "Yes"
                                )
                            && NOT 'F Orders'[UserId]
                                IN CALCULATETABLE (
                                    VALUES ( 'F Orders'[UserId] ),
                                    FILTER (
                                        ALL ( 'D Calendar'[Date] ),
                                        'D Calendar'[Date]
                                            >= MIN ( 'D Calendar'[Date] ) - 180
                                            && 'D Calendar'[Date]
                                                <= MIN ( 'D Calendar'[Date] ) - 1
                                    ),
                                    'D Order'[IsCompleted] = "Yes"
                                )
                    )
                )
            )
        )
    )
3 REPLIES 3
Super User
Super User

Re: Issue with SUMX in ISINSCOPE

Messy code like hell... 😞 Very likely it can be significantly optimized and shortened.

But I don't think anyone's gonna help you unless you provide some sample data, the model structure and show what you expect and what the actual outcome is.

It would also be useful if you could tell in your own words what the code is actually supposed to do. For instance, why is there a different formula for when one date is in scope and for when there are more than 1? Can this not be unified into one?

Best
Darek

Re: Issue with SUMX in ISINSCOPE

Hi Darek,

 

I've created the model and a report page to explain the issue; https://we.tl/t-3M2uO84dhh

 

The measure "Reactivated Users" is calculating the number of unique users that places an order on a selected date, but not placed an order in the last 40 days, but did place an order before 40 days ago. Therefor, 3 sets are prepared in variables to find if a user is in two of those sets and not in the set of orders in the last 40 days. 

 

Basically I want the month total in the table to be equal to the manual sum of all days within the month. Somehow the measure is giving a result that is not equal to the total of all days.

 

Thanks in advance for your help!

 

Regards,

Adrian

Re: Issue with SUMX in ISINSCOPE

@darlove
Did you had time to look at the pbix by any chance?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 361 members 3,575 guests
Please welcome our newest community members: