Forum Discussion

tony_tohme's avatar
tony_tohme
New Member
4 years ago
Solved

Stacked Chart from 2 tables

Hi there,

I have 2 simple tables

Table 1

YearMonthAllocated Resources
202168
202172
202143

 

And Table 2

YearMonthHired
202124
202162
202131

 

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

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

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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.

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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