The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
How to plot the cumulated "Hours Diff" start from Time(months)= 1 to 12 by Product_Year as shown in the screenshot below?
Here is the sample.
Solved! Go to Solution.
Hi @PBI_newuser ,
Sorry for the late response but I got this post lost in the middle of my other replies.
To do this you need to create a new table with the products_year and a target row use this formula:
ProductYEar = CALCULATETABLE(UNION(DISTINCT(Service[Product_Year]) , ROW("Product_Year","Target")), KEEPFILTERS(Service[Product_Year] <> BLANK())
)
Now redo your measure to :
Cumulative Hours by month =
SWITCH(SELECTEDVALUE(ProductYEar[Product_Year]),
"Target", 200* MAX(Months[Month]),
COALESCE (
CALCULATE (
SUM ( Service[Hours Diff] ),
FILTER (
ALLSELECTED ( Service[Time(months)], Service[Product_Year] ),
Service[Time(months)] <= MAX ( Months[Month] )
&& Service[Product_Year] = SELECTEDVALUE ( ProductYEar[Product_Year] )
)
),
0
))
Now replace the Product Year on your line chart by the column of the new table create final result below and in attach PBIX file:
Only question you need to be carefull is that isnce the target isd part of your table you need to use the product year from the original table to have as a slicer so that the target line does not dissapear from your chart check result attach.,
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @PBI_newuser ,
I'm looking at your file, and not really getting how is the total values suming up to give you those values, for example in product BBByear 1 you have 3 values that flatten out however that value on your table only correspond to 2 rows so how is the calculation done in order to have 3?
Can you share what are the values each of the calculation should return to have those steps in the chart?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix, sorry for the confusion. The line graph above is just an example and it was not matching with the table below. I would like to plot a line chart as shown in the screenshot below. Thanks.
Hi @PBI_newuser ,
You need to create a disconnected table with the months I created the following table:
Months = GENERATESERIES(1, 12, 1)
Now add the following measure:
Cumulative Hours by month =
COALESCE (
CALCULATE (
SUM ( Service[Hours Diff] ),
FILTER (
ALLSELECTED ( Service[Time(months)], Service[Product_Year] ),
Service[Time(months)] <= MAX ( Months[Month] )
&& Service[Product_Year] = SELECTEDVALUE ( Service[Product_Year] )
)
),
0
)
Now setup your chart with the Months from the table we created on the x-axis.
See result below and in attach file.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix , thank you for the solution!
May I know how to add one cumulative target line in the chart?
Sample as below.
Sorry once again were is that cumulative values coming from? I cannotr identify the values you present on the table.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix , sorry again for the confusion. The cumulative target is not in the pbix file I shared earlier. But I would like to add the target line with the formula "200* time(months)". I am not sure how to do it.
Hi @PBI_newuser ,
Sorry for the late response but I got this post lost in the middle of my other replies.
To do this you need to create a new table with the products_year and a target row use this formula:
ProductYEar = CALCULATETABLE(UNION(DISTINCT(Service[Product_Year]) , ROW("Product_Year","Target")), KEEPFILTERS(Service[Product_Year] <> BLANK())
)
Now redo your measure to :
Cumulative Hours by month =
SWITCH(SELECTEDVALUE(ProductYEar[Product_Year]),
"Target", 200* MAX(Months[Month]),
COALESCE (
CALCULATE (
SUM ( Service[Hours Diff] ),
FILTER (
ALLSELECTED ( Service[Time(months)], Service[Product_Year] ),
Service[Time(months)] <= MAX ( Months[Month] )
&& Service[Product_Year] = SELECTEDVALUE ( ProductYEar[Product_Year] )
)
),
0
))
Now replace the Product Year on your line chart by the column of the new table create final result below and in attach PBIX file:
Only question you need to be carefull is that isnce the target isd part of your table you need to use the product year from the original table to have as a slicer so that the target line does not dissapear from your chart check result attach.,
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @PBI_newuser ,
Since your line chart uses the ‘Legend’ label, you cannot add another measure to the ‘Values’ label.
You can create two line charts and set the background color of one of the line charts to transparent and make the two line charts overlap.
Please refer to the .pbix file.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-lionel-msft , thanks for the suggestion. However, if i take out "CCC Year3" and "DDD Year1" from "Product_Year" field, the cumulative target line seems to be incorrect on the chart as the axis range of the Cumulative hours chart has changed.
For example, in the second month, cumulative target should be 400 but it shows below 200 on the chart as the axis of Cumulative target chart will not change with the Cumulative hours chart.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |