Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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?
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]
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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
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%])
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |