Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to plot cumulative line graph by product

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.

 

 

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    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.,

     

     

9 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous ,

         

        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.