How to plot cumulative line graph by product

[PBI_newuser]

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.

[MFelix]

Hi @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?

[PBI_newuser]

Hi @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.

[MFelix]

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.

[PBI_newuser]

Hi @MFelix , thank you for the solution! 

May I know how to add one cumulative target line in the chart?

Sample as below. 

[MFelix]

Sorry once again were is that cumulative values coming from? I cannotr identify the values you present on the table.

[PBI_newuser]

Hi @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. 

[MFelix · Accepted 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.,

[v-lionel-msft]

Hi @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.

[PBI_newuser]

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.