cancel
Showing results for
Did you mean:
New Member

## Line Charts with Cumulative values

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?

1 ACCEPTED SOLUTION

Accepted Solutions
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)`

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.

Best Regards,
Angelia

12 REPLIES 12
Super User IV

I would start here:

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

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

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

Proud to be a Super User!

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.

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

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

Proud to be a Super User!

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)`

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.

Best Regards,
Angelia

Helper I

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

Frequent Visitor

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?
Super User III

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

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

Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### 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