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

Amend a running total to allow it to be filtered by date

 
Hi,

 

I have inherited a power BI file which includes a slide that has the amount of time for customers to be serviced on the x axis as a total of minutes taken. On the y axis is the count of customers going up to 100% serviced & the line is a running total.

walter_564_0-1665025189571.png

 

 

 

 

I would like to filter the line by the day of the week so that if there is a poor performance day it can be ommited from the data. The current DAX for the running total is as below 

runsum_% =
runsum_lastweek = CALCULATESUM('TTT - Last Week'[Count Trucks] ) , FILTERALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Fin_Year] = MAX( 'TTT - Last Week'[Fin_Year] ) && 'TTT - Last Week'[Turn Time Minutes] <= MAX('TTT - Last Week'[Turn Time Minutes]) )) /  CALCULATESUM( 'TTT - Last Week'[Count Trucks] ) , FILTERALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Fin_Year] = MAX( 'TTT - Last Week'[Fin_Year] )))
 
When i add in a filter it does not create a new total of 100% (1.0) i can see this when a test it by viewing it as a table
walter_564_1-1665025189941.png

 

 


 

I have read various post on here about using filter(all but it doesnt quite seem to fit with the DAX calculation above . I have tried making a simple table which just contains the relevant 7 days of the week with the below formula:

Last week Days Table = VALUES('TTT - Last Week'[Visit Start Date])
 
And then tried using VAR to use the filter(all function 
 
 

RUNSUM =  CALCULATE( SUM('TTT - Last Week'[Count Trucks] ) , FILTER( ALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Fin_Year] = MAX( 'TTT - Last Week'[Fin_Year] ) && 'TTT - Last Week'[Turn Time Minutes] <= MAX('TTT - Last Week'[Turn Time Minutes]) ))

 

 VAR

 RUNSUM2 =  CALCULATE( SUM( 'TTT - Last Week'[Count Trucks] ) , FILTER( ALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Fin_Year] = MAX( 'TTT - Last Week'[Fin_Year] )))

 

RETURN

CALCULATE(DIVIDE(RUNSUM, RUNSUM2), filter(ALL('Last week Days Table', 'Last week Days Table'[Visit Start Date]), 'Last week Days Table'[Visit Start Date] >= MAX('Last week Days Table'[Visit Start Date])

 

))

and had no luck. Data sample below:

 

walter_564_2-1665025190125.png

 


Many thanks 

 

 

 

 
3 REPLIES 3
walter_564
Frequent Visitor

If it assists below is the sample data in table format  - however with reduced rows to allow for the 20k character limit.
I have progressed as far as the below calculation but found when the information is filtered, i no longer get a total of 1

runsum_lastweek3 =

IF(HASONEVALUE('Last week Days Table'[Visit Start Date]),

CALCULATE( SUM('TTT - Last Week'[Count Trucks]) , FILTER( ALL( 'TTT - Last Week' ),'TTT - Last Week'[Visit Start Date] <= MAX( 'TTT - Last Week'[Visit Start Date]) && 'TTT - Last Week'[Turn Time Minutes] <= MAX('TTT - Last Week'[Turn Time Minutes]))) /  CALCULATE( SUM( 'TTT - Last Week'[Count Trucks] ) , FILTER( ALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Visit Start Date] <= MAX( 'TTT - Last Week'[Visit Start Date] )))

,

CALCULATE( SUM('TTT - Last Week'[Count Trucks]) , FILTER( ALL( 'TTT - Last Week' ),'TTT - Last Week'[Visit Start Date] <= MAX( 'TTT - Last Week'[Visit Start Date]) && 'TTT - Last Week'[Turn Time Minutes] <= MAX('TTT - Last Week'[Turn Time Minutes]))) /  CALCULATE( SUM( 'TTT - Last Week'[Count Trucks] ) , FILTER( ALL( 'TTT - Last Week' ) , 'TTT - Last Week'[Visit Start Date] <= MAX( 'TTT - Last Week'[Visit Start Date] )))

 

)

 

 

 

Fin_Year

Cal_Month

Cal Year

Turn Time Minutes

Count Trucks

Visit Start Date

Last Week

9

2022

4

1

27/09/2022 0:00

Last Week

9

2022

4

1

29/09/2022 0:00

Last Week

9

2022

5

1

28/09/2022 0:00

Last Week

9

2022

5

2

30/09/2022 0:00

Last Week

9

2022

6

4

26/09/2022 0:00

Last Week

9

2022

6

1

27/09/2022 0:00

Last Week

9

2022

6

1

28/09/2022 0:00

Last Week

9

2022

6

6

29/09/2022 0:00

Last Week

9

2022

6

4

30/09/2022 0:00

Last Week

9

2022

7

7

26/09/2022 0:00

Last Week

9

2022

7

9

28/09/2022 0:00

Last Week

9

2022

7

4

29/09/2022 0:00

Last Week

9

2022

7

5

30/09/2022 0:00

Last Week

9

2022

8

3

26/09/2022 0:00

Last Week

9

2022

8

3

28/09/2022 0:00

Last Week

9

2022

8

3

29/09/2022 0:00

Last Week

9

2022

8

5

30/09/2022 0:00

Last Week

9

2022

9

6

26/09/2022 0:00

Last Week

9

2022

9

4

28/09/2022 0:00

Last Week

9

2022

9

5

29/09/2022 0:00

Last Week

9

2022

9

5

30/09/2022 0:00

Last Week

9

2022

10

6

26/09/2022 0:00

Last Week

9

2022

10

1

27/09/2022 0:00

Last Week

9

2022

10

5

28/09/2022 0:00

Last Week

9

2022

10

7

29/09/2022 0:00

Last Week

9

2022

10

6

30/09/2022 0:00

Last Week

9

2022

11

8

26/09/2022 0:00

Last Week

9

2022

11

1

27/09/2022 0:00

Last Week

9

2022

11

2

28/09/2022 0:00

Last Week

9

2022

11

7

29/09/2022 0:00

Last Week

9

2022

11

5

30/09/2022 0:00

Last Week

9

2022

12

4

26/09/2022 0:00

Last Week

9

2022

12

7

28/09/2022 0:00

Last Week

9

2022

12

9

29/09/2022 0:00

Last Week

9

2022

12

4

30/09/2022 0:00

Last Week

9

2022

13

6

26/09/2022 0:00

Last Week

9

2022

13

1

27/09/2022 0:00

Last Week

9

2022

13

7

28/09/2022 0:00

Last Week

9

2022

13

5

29/09/2022 0:00

Last Week

9

2022

13

6

30/09/2022 0:00

Last Week

9

2022

14

7

26/09/2022 0:00

Last Week

9

2022

14

3

27/09/2022 0:00

Last Week

9

2022

14

9

28/09/2022 0:00

Last Week

9

2022

14

5

29/09/2022 0:00

Last Week

9

2022

14

7

30/09/2022 0:00

Last Week

9

2022

15

4

26/09/2022 0:00

Last Week

9

2022

15

2

27/09/2022 0:00

Last Week

9

2022

15

3

28/09/2022 0:00

Last Week

9

2022

15

4

29/09/2022 0:00

Last Week

9

2022

15

10

30/09/2022 0:00

Last Week

9

2022

16

6

26/09/2022 0:00

Last Week

9

2022

16

5

28/09/2022 0:00

Last Week

9

2022

16

10

29/09/2022 0:00

Last Week

9

2022

16

7

30/09/2022 0:00

Last Week

9

2022

17

4

26/09/2022 0:00

Last Week

9

2022

17

1

27/09/2022 0:00

Last Week

9

2022

17

2

28/09/2022 0:00

Last Week

9

2022

17

7

29/09/2022 0:00

Last Week

9

2022

17

8

30/09/2022 0:00

Last Week

9

2022

18

6

26/09/2022 0:00

Last Week

9

2022

18

3

27/09/2022 0:00

Last Week

9

2022

18

4

28/09/2022 0:00

Last Week

9

2022

18

7

29/09/2022 0:00

Last Week

9

2022

18

11

30/09/2022 0:00

Last Week

9

2022

19

4

26/09/2022 0:00

Last Week

9

2022

19

1

27/09/2022 0:00

Last Week

9

2022

19

5

28/09/2022 0:00

Last Week

9

2022

19

5

29/09/2022 0:00

Last Week

9

2022

19

3

30/09/2022 0:00

Last Week

9

2022

20

6

26/09/2022 0:00

Last Week

9

2022

20

3

27/09/2022 0:00

Last Week

9

2022

20

6

28/09/2022 0:00

Last Week

9

2022

20

7

29/09/2022 0:00

Last Week

9

2022

20

6

30/09/2022 0:00

Last Week

9

2022

21

8

26/09/2022 0:00

Last Week

9

2022

21

5

27/09/2022 0:00

Last Week

9

2022

21

4

28/09/2022 0:00

Last Week

9

2022

21

8

29/09/2022 0:00

Last Week

9

2022

21

8

30/09/2022 0:00

Last Week

9

2022

22

5

26/09/2022 0:00

Last Week

9

2022

22

1

27/09/2022 0:00

Last Week

9

2022

22

5

28/09/2022 0:00

Last Week

9

2022

22

3

29/09/2022 0:00

Last Week

9

2022

22

8

30/09/2022 0:00

Last Week

9

2022

23

7

26/09/2022 0:00

Last Week

9

2022

23

1

27/09/2022 0:00

Last Week

9

2022

23

2

28/09/2022 0:00

Last Week

9

2022

23

3

29/09/2022 0:00

Last Week

9

2022

23

5

30/09/2022 0:00

Last Week

9

2022

24

4

26/09/2022 0:00

Last Week

9

2022

24

1

27/09/2022 0:00

Last Week

9

2022

24

7

28/09/2022 0:00

Last Week

9

2022

24

6

29/09/2022 0:00

Last Week

9

2022

24

4

30/09/2022 0:00

Last Week

9

2022

25

7

26/09/2022 0:00

Last Week

9

2022

25

3

28/09/2022 0:00

Last Week

9

2022

25

6

29/09/2022 0:00

Last Week

9

2022

25

2

30/09/2022 0:00

Last Week

9

2022

26

5

26/09/2022 0:00

Last Week

9

2022

26

2

27/09/2022 0:00

Last Week

9

2022

26

2

28/09/2022 0:00

Last Week

9

2022

26

8

30/09/2022 0:00

Last Week

9

2022

27

3

26/09/2022 0:00

Last Week

9

2022

27

2

27/09/2022 0:00

Last Week

9

2022

27

6

28/09/2022 0:00

Last Week

9

2022

27

4

29/09/2022 0:00

Last Week

9

2022

27

5

30/09/2022 0:00

Last Week

9

2022

28

7

26/09/2022 0:00

Last Week

9

2022

28

6

27/09/2022 0:00

Last Week

9

2022

28

6

28/09/2022 0:00

Last Week

9

2022

28

3

29/09/2022 0:00

Last Week

9

2022

28

4

30/09/2022 0:00

Last Week

9

2022

29

8

26/09/2022 0:00

Last Week

9

2022

29

2

28/09/2022 0:00

Last Week

9

2022

29

4

29/09/2022 0:00


Any assistance would be really appreciated - thanks

 

walter_564
Frequent Visitor

v-xiaotang
Community Support
Community Support

Hi @walter_564 

Thanks for reaching out to us.

could you share a sample .pbix file that fully covers your question? thanks

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.