Forum Discussion

jiinson's avatar
jiinson
Icon for Helper I rankHelper I
9 years ago
Solved

Combining data in two tables and creating clustered column table

Hi   I have two tables contatining Invoice amount and Order Amount.   table 1) invoice date, invoice amount($)   table 2) order date, order amount($)   I want to create the graph looks like...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi jiinson

     

    See if you can combine the two tables into 1.  The Query Editor is probably the better place to do this, but it can be done in DAX.

     

    I suggest the format of your combined table should be three columns

     

     

    Date ,        Type ,    Amount
------------------------------
2017-01-01 , 'Invoice' , 100
2017-02-01 , 'Order'   , 200

    Then when you have the data in a single table, you'll need to create two calcluated measures.  The first will simply be the sum of the Amount column.

     

    The 2nd measure will be similar to the first but take advantage of one of the Time-intelligence functions in DAX such as SAMEPERIODLASTYEAR or PARALLELPERIOD etc.  www.daxpatterns.com is a great website for tips on the best patterns.

     

    Then just drag Type to the Axis, and your two measures to the Values area of your visual.

     

    If you need help with the DAX, let us know and we can help build that.

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi jiinson,

    As I tested, you can combine the tables to one as the following steps.

    Create a type calculated column in each table.

    Type = "invoice"

Type = "order"




    Then please click "New Table" under Modeling on home page. You can get a new table like the screenshot below.

    Table = UNION(Table1,Table2)





    Finally, create a current and last year measure based on the new table, and select the Type to the Axis, and your two measures to the Values area of your visual as Phil_Seamark posted.

    Please let me know if you have any question.

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi jiinson,

As I tested, you can combine the tables to one as the following steps.

Create a type calculated column in each table.

Type = "invoice"

Type = "order"




Then please click "New Table" under Modeling on home page. You can get a new table like the screenshot below.

Table = UNION(Table1,Table2)





Finally, create a current and last year measure based on the new table, and select the Type to the Axis, and your two measures to the Values area of your visual as Phil_Seamark posted.

Please let me know if you have any question.

Best Regards,
Angelia