Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

New user having trouble with cumulative totals.

Hi all, 

 

New to Power BI and I'm having trouble creating cumulative totals.

I want to visualise the cost accociated with certain groups and sites all in one Line Chart visual.

So grouping the cost by e.g. Site, Group and run the total based on date so I have one line for each breakdown.

Here is a set of dumpy data similar to what I am working with. 

 

DateAgentGroupCostSite
01/01/2019Tester 1ABC2400Hogwarts
02/01/2019Tester 1ABC2400Hogwarts
03/01/2019Tester 1ABC2400Hogwarts
04/01/2019Tester 1ABC2400Hogwarts
05/01/2019Tester 1ABC2400Hogwarts
06/01/2019Tester 1ABC2400Hogwarts
07/01/2019Tester 1ABC2400Hogwarts
08/01/2019Tester 1ABC2400Hogwarts
09/01/2019Tester 1ABC2400Hogwarts
10/01/2019Tester 1ABC2400Hogwarts
11/01/2019Tester 1ABC2400Hogwarts
12/01/2019Tester 1ABC2400Hogwarts
13/01/2019Tester 1ABC2400Hogwarts
14/01/2019Tester 1ABC2400Hogwarts
15/01/2019Tester 1ABC2400Hogwarts
16/01/2019Tester 1ABC2400Hogwarts
17/01/2019Tester 1ABC2400Hogwarts
18/01/2019Tester 1ABC2400Hogwarts
19/01/2019Tester 1ABC2400Hogwarts
20/01/2019Tester 1ABC2400Hogwarts
21/01/2019Tester 1ABC2400Hogwarts
22/01/2019Tester 1ABC2400Hogwarts
23/01/2019Tester 1ABC2400Hogwarts
24/01/2019Tester 1ABC2400Hogwarts
25/01/2019Tester 1ABC2400Hogwarts
01/01/2019Tester 2ABC2400Ankh-morpork
02/01/2019Tester 2ABC2400Ankh-morpork
03/01/2019Tester 2ABC2400Ankh-morpork
04/01/2019Tester 2ABC2400Ankh-morpork
05/01/2019Tester 2ABC2400Ankh-morpork
06/01/2019Tester 2ABC2400Ankh-morpork
07/01/2019Tester 2ABC2400Ankh-morpork
08/01/2019Tester 2ABC2400Ankh-morpork
09/01/2019Tester 2ABC2400Ankh-morpork
10/01/2019Tester 2ABC2400Ankh-morpork
11/01/2019Tester 2ABC2400Ankh-morpork
12/01/2019Tester 2ABC2400Ankh-morpork
13/01/2019Tester 2ABC2400Ankh-morpork
14/01/2019Tester 2ABC2400Ankh-morpork
15/01/2019Tester 2ABC2400Ankh-morpork
16/01/2019Tester 2ABC2400Ankh-morpork
17/01/2019Tester 2ABC2400Ankh-morpork
18/01/2019Tester 2ABC2400Ankh-morpork
19/01/2019Tester 2ABC2400Ankh-morpork
20/01/2019Tester 2ABC2400Ankh-morpork
21/01/2019Tester 2ABC2400Ankh-morpork
22/01/2019Tester 2ABC2400Ankh-morpork
23/01/2019Tester 2ABC2400Ankh-morpork
24/01/2019Tester 2ABC2400Ankh-morpork
25/01/2019Tester 2ABC2400Ankh-morpork
01/01/2019Tester 3ZYX2300Ankh-morpork
02/01/2019Tester 3ZYX2300Ankh-morpork
03/01/2019Tester 3ZYX2300Ankh-morpork
04/01/2019Tester 3ZYX2300Ankh-morpork
05/01/2019Tester 3ZYX2300Ankh-morpork
06/01/2019Tester 3ZYX2300Ankh-morpork
07/01/2019Tester 3ZYX2300Ankh-morpork
08/01/2019Tester 3ZYX2300Ankh-morpork
09/01/2019Tester 3ZYX2300Ankh-morpork
10/01/2019Tester 3ZYX2300Ankh-morpork
11/01/2019Tester 3ZYX2300Ankh-morpork
12/01/2019Tester 3ZYX2300Ankh-morpork
13/01/2019Tester 3ZYX2300Ankh-morpork
14/01/2019Tester 3ZYX2300Ankh-morpork
15/01/2019Tester 3ZYX2300Ankh-morpork
16/01/2019Tester 3ZYX2300Ankh-morpork
17/01/2019Tester 3ZYX2300Ankh-morpork
18/01/2019Tester 3ZYX2300Ankh-morpork
19/01/2019Tester 3ZYX2300Ankh-morpork
20/01/2019Tester 3ZYX2300Ankh-morpork
21/01/2019Tester 3ZYX2300Ankh-morpork
22/01/2019Tester 3ZYX2300Ankh-morpork
23/01/2019Tester 3ZYX2300Ankh-morpork
24/01/2019Tester 3ZYX2300Ankh-morpork
25/01/2019Tester 3ZYX2300Ankh-morpork

 

Any help would be appreciated 🙂 Hope to hear back soon,

 

Kind regards,

 

DataBruh

1 REPLY 1
Anonymous
Not applicable

-- First of all, you should have a proper DATE TABLE in your
-- model that links on dates to the Date column in your,
-- let's call it Costings - table. So, let's say the date
-- table is called Calendar (please find out how to create
-- a proper date table in a tabular model). Then hide the
-- column Date in your Costings table. Once you've done that,
-- you can use this measure:

[Cumulative Total] :=
var __lastVisibleDate = LASTDATE( Calendar[Date] )
RETURN
	CALCULATE(
		SUM( Costings[Cost] ),
		Calendar[Date] <= __lastVisibleDate,
		ALL( 'Calendar' )
	)

Best

Darek

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors