Forum Discussion

Todward's avatar
Todward
Frequent Visitor
6 years ago
Solved

Line Chart with YTD average figures

Dear all

 

I would like to create a line chart similar to the one in the screenshot below. However the figures in this chart should not be the monthly figures but the YTD average of this specific month. Means January with only January data, February with total costs of Jan and Feb divided by total volume of Jan and Feb and so on. I also added a screenshot of an excel table to demonstrate what I'm looking for.

 

Does someone has an idea how to realize this?

Thank you for your help.

 

 

 

Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey @Todward ,

    I guess you have a dedicated calendar table, which is related to your fact table. I also recommend reading this article: https://www.daxpatterns.com/time-patterns/

    I create two measures for its volume and COGS as well:

    YTD quantity = TOTALYTD(CALCULATE(SUM('Fact Sale'[Quantity])) , 'Dimension Date'[Date])

    Create a third measure that divides the previous measures accordingly.

    Hopefully, this provides what you're looking for.

    Best regards

    cat

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    Hey @Todward ,

    I guess you have a dedicated calendar table, which is related to your fact table. I also recommend reading this article: https://www.daxpatterns.com/time-patterns/

    I create two measures for its volume and COGS as well:

    YTD quantity = TOTALYTD(CALCULATE(SUM('Fact Sale'[Quantity])) , 'Dimension Date'[Date])

    Create a third measure that divides the previous measures accordingly.

    Hopefully, this provides what you're looking for.

    Best regards

    cat

    • Todward's avatar
      Todward
      Frequent Visitor
      6 years ago

      thank you TomMartens 

      worked very well!

      Viele Grüsse aus Süddeutschland

      Todward

       

       

       

  • lbeneyze's avatar
    lbeneyze
    Advocate II
    6 years ago

    Good morning,

    I'm assuming that you are using a date dimension and have marked it as date table

    I think you have to create some calculated measures to realise that:

      - YTD Volume (use Quick Measures)

      - YTD Average COGS (use Quick Measures)

      - YTD average COGS/Volume = Divide(YTD Average COGS, YTD Volume)

    Use the last measure in your chart.

     

    if you like my answer, please add a 👍

    if my answer answers your question, please mark it as a solution.

    Thanks in advance and good luck.