Simple column chart question about adding extra bars/columns from a different table column?

[kcsteele]

Hello,

I have a very simple question about the column chart visual, is it possible to add extra bars/columns from a different table/columns next to the main columns?

[v-yalanwu-msft · Accepted solution]

Hi, @kcsteele ;

You could add row by power query or use dax add rows. then create a measure. here I use dax to add rows.

1.create a new table.

New table = var _a=SUMMARIZE('Table',[Index],[Year],[percent])
var _b=SUMMARIZE('Table',"Index",MAX('Table'[Index])+1,"Year","16-20","percent",BLANK())
var _c=SUMMARIZE('Table',"Index",MAX('Table'[Index])+2,"Year","85-20","percent",BLANK())
return UNION(_a,_b,_c)

2.create a measure.

value =
SWITCH (
    MAX ( [Year] ),
    "16-20",
        CALCULATE (
            SUM ( 'Table'[percent] ),
            FILTER ( 'Table', [Year] <= 20 && [Year] >= 16 )
        ),
    "85-20", CALCULATE ( SUM ( 'Table'[percent] ), FILTER ( 'Table', [Year] <= 20 ) ),
    MAX ( [percent] ))

The final output is shown below:

If not right ,please share more details.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kcsteele]

Hi v-yalanwu-msft,

Thanks so much for your help, I was able to achieve it by performing similar, however your solution may be a bit more elegant. I also created a separate "years" table with the added 85-20 and 16-20 rows, and then I created a new table for the values by taking the additional "16-20" and "85-20" columns from the mainData table, adding those columns along with the normal years 2016 - 2020 values columns. Then unpivoting all those columns, setting up the relationships and assigning the values to the well.

[kcsteele]

I guess I will try and add "16-20" and "85-20" as additional "years" to the "years" table and go from there? And then maybe use conditional formatting on the years. I was thinking if it's just possible out of the box without any of this finagling? Maybe with a different visual someone knows about.