Stacked Chart from 2 tables

[tony_tohme]

Hi there,

I have 2 simple tables

Table 1

Year	Month	Allocated Resources
2021	6	8
2021	7	2
2021	4	3

And Table 2

Year	Month	Hired
2021	2	4
2021	6	2
2021	3	1

How can I represent these 2 tables in one chart side by side compare grouped by month?

When I add one table it works and show the breakdown by month, but when I add the second table  'Hired' as values it sums up the values and does it break it down beside the first data.

Your input is much appreciated.

thank you

[v-luwang-msft · Accepted solution]

Hi @tony_tohme ,

The final output refer:

create the column on the two table:

yearmonth = 'Table'[Year]&""&'Table'[Month]

Column = 'Table (2)'[Year]&""&'Table (2)'[Month]

Then create the below table:

Table3 =
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            'Table',
            "year", 'Table'[Year],
            "month", 'Table'[Month],
            "my", 'Table'[yearmonth]
        ),
        SELECTCOLUMNS (
            'Table (2)',
            "year", 'Table (2)'[Year],
            "month", 'Table (2)'[Month],
            "my", 'Table (2)'[Column]
        )
    )
)

Relationship like below:

Then create the visual:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@tony_tohme , Either create a common month year table or Date table and join with that and analyze other 

In both table you can have one of the column

Month Year = [Year]*100 +[Month]

or

Date = date([year],[month],1)

Join both with date table to month year and use that for analysis 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.