cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Line Charts with Cumulative values

 I have my data like this (image below)

 

Capture.PNG

Now i need to create a line chart in Power BI. I am getting something like this (sample image below)

 

Capture.PNG

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

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)


1.PNG

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.

2.PNG

Please feel free to ask if you any issue.

Best Regards,
Angelia

View solution in original post

12 REPLIES 12
Super User IV
Super User IV

I would start here:

http://www.daxpatterns.com/cumulative-total/

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
Super User IV

Actually, if you have the April release, you could just use a Running Total Quick Measure. Right click your "Spend" column to create.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Microsoft
Microsoft

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)


1.PNG

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.

2.PNG

Please feel free to ask if you any issue.

Best Regards,
Angelia

View solution in original post

Hiiii

I have data like this

NumberCompanyDateOperating RevenueProfit after TaxROCE (%)Operating Margin (%)Net Margin (%)EPS Growth (%)
1TIL(Grove)17-Mar325.3126.4514.3762.55.3160.68
  16-Mar1,736.27-43.599.436.68-2.5-1,023.51
  15-Mar1,471.564.7211.7410.650.32-52.47
  14-Mar1,312.589.9310.149.450.75130.39
  13-Mar1,173.654.318.498.250.36-71.9
  12-Mar1,381.6015.3410.386.471.1-74.51
  11-Mar1,376.1060.1924.569.834.351.12
2Eicher7-Mar031.3771.04-99.461,928.10
  6-Mar01.555.78-34.5-51.39
  5-Mar03.1811.16-84.79102.12
  4-Mar02.861.76-94.39131.39
  3-Mar555.37-9.113.546.48-1.6217.26
  2-Mar------
  1-Mar------

 

I want company name as y axis and 

DateOperating RevenueProfit after TaxROCE (%)Operating Margin (%)Net Margin (%)EPS Growth (%)

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

 

 

Thank you

 

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?

Hi,

Share some data and show the expected result in a simple table format.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The below image shows the values cumulating correctly but with the Fiscal Period on the X-Axis. Note the table is showing the correct details.

BlueMan_0-1600914269433.png

If i change the X-Axis to Month Name the Chart changes and is not cumulative.

 

BlueMan_1-1600914412471.png

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors