Combining data in two tables and creating clustered column table

[jiinson]

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

I know how to set up the measures of prior year and current year for each invoice values and order values. I want to know how to set up x-axis with invoice and order. I have a date table linked with invoice date and order date.

Regards,

Jiin

[Phil_Seamark · Accepted solution]

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 · Accepted solution]

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

[Phil_Seamark · Accepted solution]

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.