I have my data like this (image below) Now i need to create a line chart in Power BI. I am getting something like this (sample image below) Just for illustration i used this chart (Target and Actuals are two lines). Now i need the lines to be cumulative over months. How can i do that in Power BI?

Hi GaRaGe,First, you should create a calculated column to get a number format month. If we use the Jan, Feb, Mar and so on. Month will order by alphabet. Month! = SWITCH(Test[Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,12) Then create two measure for cumulative spend over month. Target = CALCULATE(SUM(Test[Spend]),FILTER(ALL(Test),Test[Month!]<=MAX(Test[Month!])&&Test[Metric]="Target")) Actual = CALCULATE(SUM(Test[Spend]),FILTER(ALL(Test),Test[Month!]<=MAX(Test[Month!])&&Test[Metric]="Actual")) Finally, create a line chart, select the [month!] field as axis, the two measures as value level.Please feel free to ask if you any issue.Best Regards,Angelia

Line Charts with Cumulative values | Microsoft Fabric Community

26 Replies

v-huizhn-msft

Microsoft Employee

9 years ago

Hi GaRaGe,

First, you should create a calculated column to get a number format month. If we use the Jan, Feb, Mar and so on. Month will order by alphabet.

Month! = SWITCH(Test[Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,12)

Then create two measure for cumulative spend over month.

Target = CALCULATE(SUM(Test[Spend]),FILTER(ALL(Test),Test[Month!]<=MAX(Test[Month!])&&Test[Metric]="Target"))

Actual = CALCULATE(SUM(Test[Spend]),FILTER(ALL(Test),Test[Month!]<=MAX(Test[Month!])&&Test[Metric]="Actual"))

Finally, create a line chart, select the [month!] field as axis, the two measures as value level.

Please feel free to ask if you any issue.

Best Regards,
Angelia

sushmasushma

Helper I

8 years ago

Hiiii

I have data like this

Number	Company	Date	Operating Revenue	Profit after Tax	ROCE (%)	Operating Margin (%)	Net Margin (%)	EPS Growth (%)
1	TIL(Grove)	17-Mar	325.31	26.45	14.37	62.5	5.3	160.68
		16-Mar	1,736.27	-43.59	9.43	6.68	-2.5	-1,023.51
		15-Mar	1,471.56	4.72	11.74	10.65	0.32	-52.47
		14-Mar	1,312.58	9.93	10.14	9.45	0.75	130.39
		13-Mar	1,173.65	4.31	8.49	8.25	0.36	-71.9
		12-Mar	1,381.60	15.34	10.38	6.47	1.1	-74.51
		11-Mar	1,376.10	60.19	24.56	9.83	4.35	1.12
2	Eicher	7-Mar	0	31.37	71.04	-	99.46	1,928.10
		6-Mar	0	1.55	5.78	-	34.5	-51.39
		5-Mar	0	3.18	11.16	-	84.79	102.12
		4-Mar	0	2.86	1.76	-	94.39	131.39
		3-Mar	555.37	-9.11	3.54	6.48	-1.62	17.26
		2-Mar	-	-	-	-	-	-
		1-Mar	-	-	-	-	-	-

I want company name as y axis and

Date

Operating Revenue

Profit after Tax

ROCE (%)

Operating Margin (%)

Net Margin (%)

EPS Growth (%)

This row i want x axis it is possible Plz suggest me

Thank you

BlueMan
Frequent Visitor
5 years ago
I know this is an old thread but i would like to ask a question on your solution. I have setup a measure to use COUNTA to total the number of tickets. I create a Matrix table and add Fiscal Year and Fiscal Period followed by adding the CountA measure. This is working as expected. I create a new measure see below that references the COUNTA measure to workout the cumulative totals for each Fiscal Period. I add this measure to the table and it is working as expected. Great.

IncidentCumulative = IF(COUNTROWS(Sheet1)>0,
CALCULATE(
[TotalTickets],
FILTER(ALLSELECTED(DIM_Date[Fiscal Period]),
DIM_Date[Fiscal Period] <=MAX (DIM_Date[Fiscal Period])
)
))

My issue comes down to the Line chart. I create a new Line chart add Month Name (Sorted by Fiscal Period) in the X-Axis and then add the cumulative measure in the Value. Fiscal Year is has also been added into the Legend field. The data is no cumulative, however if i change the X-Axis to Fiscal Period, same as you have done the line chart shows the cumulative values. How do i have a line chart that will show the cumulative values using Month Name in the X-Axis?
- Ashish_Mathur
  Super User
  5 years ago
  Hi,
  
  Share some data and show the expected result in a simple table format.
PowerBIPilgrim
Helper II
5 years ago
I have a question on the solution, I'm getting a cumalitive result fine, but I want it just for this year, as opposed to going as far back as the equivalent of the test[spend] table goes

If I date a Date filter, it still sums from the beginning of the table, as opposed to all results for the current year.

Greg_Deckler
Community Champion
9 years ago
I would start here:
http://www.daxpatterns.com/cumulative-total/
Greg_Deckler
Community Champion
9 years ago
Actually, if you have the April release, you could just use a Running Total Quick Measure. Right click your "Spend" column to create.
mwinds
Helper I
3 years ago
Hi all,

I've having issues with this measure. The line chart is cumulative for some months but not for others. Here's what I've got.

Actual Cumulative = CALCULATE(SUM(InvoiceDetails[Total LAD Funding]),FILTER(ALL('Date'),'Date'[Date]<=MAX(InvoiceDetails[Month invoiced])))

Budget Cumulative = CALCULATE(SUM(Spendperlot[Total]),FILTER(ALL('Date'),'Date'[Date]<=MAX(InvoiceDetails[Month invoiced])))

The figures are contained in separate tables and should include all of the values in those tables.
Chart looks like this.

I've set up a date table following this guide https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables and looks like this for example:

Data in the Month Invoiced table looks like this

Has any one got ideas to why this isn't working properly?
- Ashish_Mathur
  Super User
  3 years ago
  Hi,
  
  You should have a relationship (Many to One and Single) between the Month invoiced column and the Date column of the Date Table. Ensure that the Month name column is sorted by the Month number column in the Date table. To the X-axis of your visual, drag Year and Month name from the Date table. Write these measures:
  
  Actual Cumulative = CALCULATE(SUM(InvoiceDetails[Total LAD Funding]),datesbetween('Date'[Date],minx(all('Date'[Date]),'Date'[Date]),max('Date'[Date])))
  
  Hope this helps.
  - mwinds
    Helper I
    3 years ago
    Hi there,
    
    No luck im afriad. Chart looks like this
    
    Here's the link to my file https://drive.google.com/file/d/1-litA5nfaKu4OPcpEVEGnKJG3E4sfA8E/view?usp=sharing
    
    Any help is appreciated.