cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brotedo
Frequent Visitor

Running Total Works Referencing Just 1 table but breaks when I add a relationship to another?

I'm using a matrix, and want sales values to sum along columns that represent a rolling 12 month view. My matrix has two rows for User and Team that should filter the data. Originally, the table with my sales data didn't have user and team information, it just had a UserId with a relationship to a UserKey table that connected that Id to Name and Team. I tried the following code to make a running total with that setup:

RT Test = 
    Var DateMax = max('Planning Monthly'[DateDiff])
Return
    sumx(
        filter(
            allexcept('Planning Monthly','Planning Monthly'[UserId]), 'Planning Monthly'[DateDiff]<= DateMax),
                [Rolling12Value])

UserId is the column that creates the relationship, but this didn't work. It did create a running total, but it just ignored the row values (gave the same running total for each user/team combo).

 

I pulled the UserName and Team into my original table using Lookupvalue and modified the code to this and it works:

RT Test = 
    Var DateMax = max('Planning Monthly'[DateDiff])
Return
    sumx(
        filter(
            allexcept('Planning Monthly','Planning Monthly'[UserName],'Planning Monthly'[Team]), 'Planning Monthly'[DateDiff]<= DateMax),
                [Rolling12Value])
                        

 I'd really rather keep using the two tables with the relationship, rather than pulling name and team into my sales table, but the only code I've gotten to work just uses the one table.

1 ACCEPTED SOLUTION
Barthel
Solution Sage
Solution Sage

Hey,

In some cases, especially with matrices, ALLEXCEPT does not work properly. That might be the cause of your problem. Maybe this will work:

RT Test =
VAR DateMax =
    MAX ( 'Planning Monthly'[DateDiff] )
RETURN
    SUMX (
        CALCULATETABLE (
            'Planning Monthly',
            REMOVEFILTERS ( 'Planning Monthly' ),
            VALUES ( 'Planning Monthly'[UserId] ),
            'Planning Monthly'[DateDiff] <= DateMax
        ),
        [Rolling12Value]
    )

Read this article for more info: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ 

View solution in original post

3 REPLIES 3
Barthel
Solution Sage
Solution Sage

Hey,

In some cases, especially with matrices, ALLEXCEPT does not work properly. That might be the cause of your problem. Maybe this will work:

RT Test =
VAR DateMax =
    MAX ( 'Planning Monthly'[DateDiff] )
RETURN
    SUMX (
        CALCULATETABLE (
            'Planning Monthly',
            REMOVEFILTERS ( 'Planning Monthly' ),
            VALUES ( 'Planning Monthly'[UserId] ),
            'Planning Monthly'[DateDiff] <= DateMax
        ),
        [Rolling12Value]
    )

Read this article for more info: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ 

Thank you so much! This works, and it updates a lot faster than what I had originally used as well!

Hi @Barthel 
You are 100% correct. In general using REMOVEFILTERS + VALUES is the best option when creating measures.
Actually @Brotedo is using ALLEXCEPT as a table not as a CALCULATE modifier which is not a good practice

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors