Weekly graph data and YTD data in one chart

[erdinch]

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 YearToDate data. As I do not know how to do that currently I am using second graph that is showing the data on a yearly basis.Unfortunately it does not look nice. Could you please advice me how to incorporate both bars in one graph?

Thanks

[v-gizhi-msft]

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]

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 · Accepted solution]

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]

can i get video of steps how its done

[amitchandak]

Not a direct way I am away of, But you can try a new table using summarize and do it

new table = union(summarize(table,table[Month],"Value",sum(table[Value])),summarize(table,table[Month],"Value",[YTD Value]))

Assumed you have ytd formula outside

Use this to display that chart