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

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.