How to display YTD + Target as a seperate column

[Josh1994]

Hi!

I am trying to copy an excel graph into a power bi visual, but am struggling to get the functionality i want.

I want a clustered column chart to show monthly information, and then two seperate columns at one side of the graph displaying 2021 data, and the YTD average data for the months.

Below I attach screenshot of how i want the graph to look.

This is where i currently am

I have the target data, and the last year date as a seperate column in data.

Really appreciate yoru help, please let me know if i am not clear.

Thank you

Josh

[Josh1994 · Accepted solution]

Hi Guys,

I ended up solving this by using a column named "sort order" and then sorting the other column by that column.

print screen below

thanks for your help!

[Anonymous]

Hi @Josh1994 ,

It seems that there are additional two values for X-axis except all Months, right?

If so, you need to create a new table for X-axis values:

For X-axis = 
var _t1=ADDCOLUMNS( VALUES('Table'[Date].[Month]),"Index",MONTH( CONVERT([Date].[Month] &" 1",DATETIME)))
return UNION(_t1,ROW("Month","YTD","Index",13), ROW("Month",MAX('Table'[Date].[Year])-2 ,"Index",14) )

Sort values by Index column:

Then create a measure to match each "Month":

Measure = 
var _SumofMonth=CALCULATE(SUM('Table'[Value]),FILTER('Table',MONTH([Date])=MAX('For X-axis'[Index]) && YEAR([Date])=MAXX(ALL('Table'[Date]),YEAR([Date])) ))
RETURN 
SWITCH(MAX('For X-axis'[Month]),"YTD",[YTD],"2020",[Two years before],_SumofMonth)

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Josh1994 Seems like  you would need a disconnected table with the categories you want in your axis and then a measure that looks at what category it is in and returns the correct value. 

Here is an example of the Disconnected Table Trick: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...