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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
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

  • sushmasushma's avatar
    sushmasushma
    Helper I
    8 years ago

    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

     

  • BlueMan's avatar
    BlueMan
    Frequent Visitor
    5 years ago

    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?
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      Hi,

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

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

         

  • PowerBIPilgrim's avatar
    PowerBIPilgrim
    Helper II
    5 years ago

    I have a question on the solution, I'm getting a cumalitive result fine, but I want it just for this year, as opposed to going as far back as the equivalent of the test[spend] table goes

    If I date a Date filter, it still sums from the beginning of the table, as opposed to all results for the current year.