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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Make the first relationship active.

  • 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.