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

BlueMan's avatar
BlueMan
Frequent Visitor
5 years ago

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.

 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

Hi,

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      Hi,

      You may download my PBI file from here.

      Hope this helps.

      • BlueMan's avatar
        BlueMan
        Frequent Visitor
        5 years ago

        thanks that fixed it using DATESYTD, but why wouldn't the other method work? 

  • SinatraB's avatar
    SinatraB
    Frequent Visitor
    4 years ago

    Hi, I have the same problem. If I send you the project could you help me?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      Hi,

      Share some data, describe the question and show the expected result.

      • SinatraB's avatar
        SinatraB
        Frequent Visitor
        4 years ago

        First of all, thank you for your availability. Basically I have a graph that takes the costs of various locations of my company, I copy in a column only the costs of projects with ROI <2 years and then on the graph I can filter them by site (plant), by project phase (stage) and by date (the project completion dates are entered on the x axis). What I want to get is the cumulative value of the costs. Apparently the chart works well with these filters:

         

         

        but when I select the filters differently, this happens:

         

        What am I doing wrong? Thanks in advance.

        Link to download the project: https://www.dropbox.com/s/txaqxv83llcjfp0/Test.pbix?dl=0