Running totals with multiple related tables

[Jerome453]

I am trying to calculate running totals for sales from a few linked by IDs tables by using the following:

Amount Measure = SUMX( Sales, Sales[Amount] )

Running Total = 
VAR MinAmount = 
    MINX( ALLSELECTED(Sales[Client ID]), [Amount Measure] )
VAR RT = 
    CALCULATE(
        SUMX(
            FILTER(ALLSELECTED(Sales[Client ID]), [Amount Measure] >= MinAmount),
            [Amount Measure]
        ),
        CALCULATETABLE(ALL(Clients))
    )
RETURN
    RT

but the resulting table shows all of the clients for the selected employee.

What can be done to show only the selected employee clients?

Sample data

Employees table:

Employee ID	Employee Name	Target Amount
1	Donna Harper	12000
2	Tyrese Coffey	11000
3	Gisselle Bonilla	15000
4	Patrick Leach	10000

Clients table:

Client ID	Client Name
1	Harris - Beatty
2	Kohler, Terry and Waters
3	Walsh - Hilpert
4	Waelchi LLC
5	Spinka LLC
6	Mills, Wyman and Yundt
7	Nienow - Miller
8	Armstrong - Steuber
9	Schultz - Bogisich

Sales table:

Date	Salesperson ID	Client ID	Amount
01.01.2023	1	3	150
01.01.2023	2	4	5000
01.01.2023	3	5	600
01.01.2023	4	3	75
01.01.2023	1	4	200
01.01.2023	2	5	954
01.01.2023	3	3	147
01.01.2023	4	4	2223
01.01.2023	1	5	478
02.01.2023	2	3	200
02.01.2023	3	4	653
02.01.2023	4	5	258
02.01.2023	1	3	552
02.01.2023	2	4	478
02.01.2023	3	5	269
02.01.2023	4	6	3888

[MFelix · Accepted solution]

Hi @Jerome453 ,

Let me start by refering that the Amount measure does not need to be a SUMX you can simply do a SUM, SUMX is used when you want to have a calculation maintaining the row context of the table in this case there is no need, regarding the Running total you can try and replace your code by the one below:

Amount Measure = SUM( Sales[Amount] )

Running Total = 
VAR MinAmount = 
    MINX( ALLSELECTED(Sales[Client ID]), [Amount Measure] )
VAR RT = 
    CALCULATE(
        SUMX(
            FILTER(ALLSELECTED(Sales[Client ID]), [Amount Measure] >= MinAmount),
            [Amount Measure]
        ),
        CALCULATETABLE(ALL(Clients))
    )
RETURN
    RT

On a best practice note you should use the dimension tables and only the columns you need and not the sales table redo the measure to:

Running Total = 
VAR MinAmount = 
    MINX( ALLSELECTED(Clients[Client ID]), [Amount Measure] )
VAR RT = 
    CALCULATE(
        SUMX(
            FILTER(ALLSELECTED(Clients[Client ID]), [Amount Measure] >= MinAmount),
            [Amount Measure]
        ),
        CALCULATETABLE(ALL(Clients[Client ID], Clients[Client Name]))
    )
RETURN
  IF([Amount Measure] <> BLANK(),   RT)

Same result:

But be aware that this logic is only working when you select a single employee if you select more than one the the runnig total is incorrect.

If you want the running total to work with all the values redo the measure to:

Running Total = 
VAR MinAmount = 
    MINX( ALLSELECTED(Clients[Client ID]), [Amount Measure] )
VAR RT = 
    CALCULATE(
        SUMX(
            FILTER(SUMMARIZE(Sales, Clients[Client ID], Employees[Employee ID]), [Amount Measure] >= MinAmount),
            [Amount Measure]
        ),
        ALLSELECTED(Clients[Client ID], Clients[Client Name]), ALLSELECTED(Employees)
    )
RETURN
  IF([Amount Measure] <> BLANK(),   RT)

Check PBIX file attach.

[Jerome453]

Hello @MFelix,
Thank you for the response. Your solution works. Still, i noticed that when more than one employee is selected the grand total for running total is incorrect using your latest modification.

How would one go about fixing that?

[MFelix]

Hi @Jerome453,

Sorry for the late response try the following code:

Running Total =
VAR MinAmount =
    MINX ( ALLSELECTED ( Clients[Client ID] ), [Amount Measure] )
VAR RT =
    CALCULATE (
        SUMX (
            FILTER (
                SUMMARIZE ( Sales, Clients[Client ID], Employees[Employee ID] ),
                [Amount Measure] >= MinAmount
            ),
            [Amount Measure]
        ),
        ALLSELECTED ( Clients[Client ID], Clients[Client Name] ),
        ALLSELECTED ( Employees )
    )
RETURN
    IF (
        ISINSCOPE ( Clients[Client Name] ) || ISINSCOPE ( Clients[Employee Name] ),
        IF ( [Amount Measure] <> BLANK (), RT ),
        [Amount Measure]
    )

[Jerome453]

Thank you @MFelix ,
I am not familiar with ISINSCOPE(), but i did find a solution by using HASONEVALUE().

On a related issue, im trying to count the clients under a certain % RT out of total amount by using

% RT From Total Amount = 
VAR TotalAmount = 
    CALCULATE(
        SUMX(
            SUMMARIZE(Sales, Clients[Client ID], Employees[Employee ID]),
            [Amount Measure]
        ),
        ALLSELECTED(Clients[Client ID], Clients[Client Name]), ALLSELECTED(Employees)
    )
RETURN
    DIVIDE([Running Total], TotalAmount)

Count Top 95% = 
VAR TopClients =
    CALCULATETABLE (
        FILTER (
            SUMMARIZE (
                Sales,
                Employees[Employee ID],
                Clients[Client ID],
                Clients[Client Name]
            ),
            [% RT From Total Amount] <= 0.95
        ),
        ALLSELECTED ( Clients[Client ID], Clients[Client Name] )
    )
RETURN
    IF ( [Amount Measure] <> BLANK (), COUNTROWS ( TopClients ) )

but the total is not correct and i dont know how to calculate it.

Any advice would be great.
Link to sample pbix file

https://drive.google.com/file/d/1M22AgOaKl1_q3656hp92N_pBnQdHQ2Xq/view?usp=sharing

[MFelix]

Hi @Jerome453 ,

If you want only the values for the employee name try the following measure:

Total Rows= SUMX(VALUES(Employees[Employee ID]), [Count Top 95%])

[Jerome453]

Thanks @MFelix , this is exactly what i needed!