Forum Discussion

kcsteele's avatar
kcsteele
Helper I
4 years ago
Solved

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

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?

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

3 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    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's avatar
      kcsteele
      Helper I
      4 years ago

      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's avatar
    kcsteele
    Helper I
    4 years ago

    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.