Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 chart (Target and Actuals are two lines).
Now i need the lines to be cumulative over months. How can i do that in Power BI?
Solved! Go to Solution.
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
Hi,
You may download my PBI file from here.
Hope this helps.
thanks that fixed it using DATESYTD, but why wouldn't the other method work?
You are welcome. If my previous reply helped, please mark that as Answer. You were simply not using the correct function.
Hiiii
I have data like this
Number | Company | Date | Operating Revenue | Profit after Tax | ROCE (%) | Operating Margin (%) | Net Margin (%) | EPS Growth (%) |
1 | TIL(Grove) | 17-Mar | 325.31 | 26.45 | 14.37 | 62.5 | 5.3 | 160.68 |
16-Mar | 1,736.27 | -43.59 | 9.43 | 6.68 | -2.5 | -1,023.51 | ||
15-Mar | 1,471.56 | 4.72 | 11.74 | 10.65 | 0.32 | -52.47 | ||
14-Mar | 1,312.58 | 9.93 | 10.14 | 9.45 | 0.75 | 130.39 | ||
13-Mar | 1,173.65 | 4.31 | 8.49 | 8.25 | 0.36 | -71.9 | ||
12-Mar | 1,381.60 | 15.34 | 10.38 | 6.47 | 1.1 | -74.51 | ||
11-Mar | 1,376.10 | 60.19 | 24.56 | 9.83 | 4.35 | 1.12 | ||
2 | Eicher | 7-Mar | 0 | 31.37 | 71.04 | - | 99.46 | 1,928.10 |
6-Mar | 0 | 1.55 | 5.78 | - | 34.5 | -51.39 | ||
5-Mar | 0 | 3.18 | 11.16 | - | 84.79 | 102.12 | ||
4-Mar | 0 | 2.86 | 1.76 | - | 94.39 | 131.39 | ||
3-Mar | 555.37 | -9.11 | 3.54 | 6.48 | -1.62 | 17.26 | ||
2-Mar | - | - | - | - | - | - | ||
1-Mar | - | - | - | - | - | - |
I want company name as y axis and
Date | Operating Revenue | Profit after Tax | ROCE (%) | Operating Margin (%) | Net Margin (%) | EPS Growth (%) |
This row i want x axis it is possible Plz suggest me
Thank you
Actually, if you have the April release, you could just use a Running Total Quick Measure. Right click your "Spend" column to create.
I would start here:
http://www.daxpatterns.com/cumulative-total/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |