Forum Discussion

GaRaGe's avatar
GaRaGe
New Member
9 years ago
Solved

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 c...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    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

mwinds's avatar
mwinds
Helper I
3 years ago

Super. Thanks. 

However the chart looks like this 

The chart goes go up and seems to be ignoring every other month using continuous. 

Or looks like this if categorical shows every day individually.

 

I'm looking for it to show every month on the x axis so the chart goes up gradually rather than in chunks.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

  • mwinds's avatar
    mwinds
    Helper I
    3 years ago

    Thanks so much for your help.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      3 years ago

      You are welcome.  If my previous reply helped, please mark that reply as Answer.