Forum Discussion
Power ParticipantLine Chart carrying out "SUM" function on values!
I have a line chart that has the below values...
Axis: Snapshotdate {Date}
Legend: Project Name {Text}
Values: Completed (Decimal | Percentage | Dont Summarise)
The problem is the "Completed" values are being added together, where i want the individual completed percent values for each month showing, but it doesnt give me the option to "Dont Summerize" on the "Values" dropdown option. I hope that makes sense.
StuartSmith let's assume the case here, if a project has two dates for the same month let's say 50% and then 99%, currently it is doing the sum of this ( correct? ), what would you like to see in this case 99% (I guess)
so add a measure :
Max % = MAX ( Table[Complete Percentage Column] )and use this measure in the value, basically, it will show the maximum complete % of a project if there are multiple entries of the same project in a month. I hope it helps.
For date, on the formatting pane, under the x-axis choose continuous instead of categorical.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
6 Replies
StuartSmithI have just found out that you can format a measure from the "Relationship" tab...
Anyway, thanks for your original measure fix, that started me on the path to a solution that I am happy with.
- parry2k
Super User
StuartSmith You cannot do that, you have to have an aggregation on the value section. Are you saying, you have projects with the same snapshot date with percentage?
- StuartSmith
Thanks for replying. And yes, there will be multiple projects with the same "Snapshot Date" and varying % values. I was just reading up on "Aggregates" on the link https://docs.microsoft.com/en-us/power-bi/create-reports/service-aggregates and at the bottom is says about if the data is from Excel and a calculated value. The data is from an excel file, but not created via a formula.
I was just hoping to create a line chart of the progress of each project over the months. Here is an example of the data.
and I have also noticed if I select a data point, the "Month" is wrong (see below).
How can I get it so it just show a line for each project and the monthly % value? Thanks in advance.
- parry2k
StuartSmith let's assume the case here, if a project has two dates for the same month let's say 50% and then 99%, currently it is doing the sum of this ( correct? ), what would you like to see in this case 99% (I guess)
so add a measure :
Max % = MAX ( Table[Complete Percentage Column] )and use this measure in the value, basically, it will show the maximum complete % of a project if there are multiple entries of the same project in a month. I hope it helps.
For date, on the formatting pane, under the x-axis choose continuous instead of categorical.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- StuartSmith
Thanks, and we are getting there. I did change your code from "Max" to "Avg" as say if one country was on 100% completed and another on 50% completed, the line chart would give the impression that all countries where 100% completed, where as changing to Avg, it will give a Avg for all Countries. Plus if only 1 country is selected, the values show correctly.
I will test, but its looking good. One thing I did nortice was that by adding the measure, its changed the formatting of the chart from 40% to 0.40 and the Axis range has gone from 0% > 100% to 0.0 > 1.0. Is there a way to format the measure output?
Before
After
- StuartSmith
I presumed...
Avg % = FORMAT(AVERAGE(ProjectCompletionByCountry[Complete] ), "0.00%")would work, but the chart visual fails to display.