Forum Discussion

erdinch's avatar
erdinch
Helper I
6 years ago
Solved

Weekly graph data and YTD data in one chart

Hello, I am having one monthly measure refelected in bar chart and target shown as line.  That measure1 is calculated based on data in table1. at the end of the same graph I would like to have Year...
Need Help
Show and Tell
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try to create a new table by Enter Data:

    Create the relationship with original table by one-to-many:

    Then create a measure:

    Measure 2 = IF(MAX('Table (2)'[Month])="TotalAverage",CALCULATE(SUM('Table'[Sales]),ALLSELECTED('Table')),SUM('Table'[Sales]))

    Choose the new table's [Month] and this measure as clustered column chart.

    If you want to sort Month from 'January' to 'December', please add an index column in Query Editor.

    After Apply&Close, sort [Month] by [Index], the result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

According to your description, i create a table to test:

Then create a measure:

Measure = IF(MAX('Table'[Date].[Month])="January"||MAX('Table'[Date].[Month])="February",SUM('Table'[Sales]),IF(MAX('Table'[Date].[Month])="December",CALCULATE(SUM('Table'[Sales]),ALLSELECTED('Table')),0))

Choose Clustered Column Chart, and it shows:

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

  • erdinch's avatar
    erdinch
    Helper I
    6 years ago

    Hello,

    Thanks for the hint but it should be altered slighly. What I need is cummulative data to be shown AFTER December. So if we have data for Jan (239) and Feb (260) at the end after Dec. There should be new YTD column showing 499. like in the pic below:

     

     

    • v-gizhi-msft's avatar
      v-gizhi-msft
      Community Support
      6 years ago

      Hi,

       

      Please try to create a new table by Enter Data:

      Create the relationship with original table by one-to-many:

      Then create a measure:

      Measure 2 = IF(MAX('Table (2)'[Month])="TotalAverage",CALCULATE(SUM('Table'[Sales]),ALLSELECTED('Table')),SUM('Table'[Sales]))

      Choose the new table's [Month] and this measure as clustered column chart.

      If you want to sort Month from 'January' to 'December', please add an index column in Query Editor.

      After Apply&Close, sort [Month] by [Index], the result shows:

      Here is my test pbix file:

      pbix 

      Hope this helps.

       

      Best Regards,

      Giotto Zhi

      • viji_2611's avatar
        viji_2611
        New Member
        3 years ago

        can i get video of steps how its done