cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sajith Frequent Visitor
Frequent Visitor

How to display data from two tables (one table created using query editor)

Hi All,

 

Kindly seeking your help to solve below issue.

 

I need to display actual sales vs. target daily. Actual sales are extracted from the "Sales" table & another table for "Target"  which is created using the query editor (which is not visible in the merge/append options for your information).

Two tables are connected with 'Date' as a table relationship. 

 

Sales  table

Date             Sales   Sales Rep

1-1-2019       100        David

1-2-2019        200       James 

etc...

Target table

Date              target    Sales Rep

1-1-2019      150         James

1-2-2019       200        Flinch 

etc..

 

Now I need to see Daily actual sales vs. target on the same chart and also a "Sales rep" as a filter option.

I was searching many threads but still couldn't find an exact solution for this.

 

Thank you

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to display data from two tables (one table created using query editor)

Hi,

Try this:

  1. Create a Calendar Table and build a relationship from the Date column of the 2 Tables to the Date column of the Calendar Table
  2. Create another Table with unique Sales reps and build a relationship from the Sales rep column of the 2 Tables to the Sales rep column of the new Table
  3. To your slicer visual, drag the Sales rep column to the slicer
  4. To the Table visual, drag Date from the Calendar Table
  5. Write these measures

Actual sales = SUM(Sales[Sales])

Target sales = SUM(Target[Sales])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Mvignesh53 Member
Member

Re: How to display data from two tables (one table created using query editor)

Hi,

 

Have you tried using the DAX Function LOOKUPVALUE(). You can add a new column in the Sales Table and use the Function like

LOOKUPVALUE(Target[Target], Target[Date], Sales[Date]). This should bring it in as a new column in the sales Table.

 

Else if you dont want to affect the sales Table, then Create a new Table with the Below Expression:

SUMMARIZECOLUMNS(Sales[Date], Sales[Sales],"Target",CALCULATE(MAX(Target[Target], TREATAS(VALUES(Sales[Date]), Target[Date))))

 

Basically, the TREATAS function would use the values of other Tables as the filter Values of the Current Table.

 

 

sajith Frequent Visitor
Frequent Visitor

Re: How to display data from two tables (one table created using query editor)

Thanks for the reply.  will check revert soon.


@Mvignesh53 wrote:

Hi,

 

Have you tried using the DAX Function LOOKUPVALUE(). You can add a new column in the Sales Table and use the Function like

LOOKUPVALUE(Target[Target], Target[Date], Sales[Date]). This should bring it in as a new column in the sales Table.

 

Else if you dont want to affect the sales Table, then Create a new Table with the Below Expression:

SUMMARIZECOLUMNS(Sales[Date], Sales[Sales],"Target",CALCULATE(MAX(Target[Target], TREATAS(VALUES(Sales[Date]), Target[Date))))

 

Basically, the TREATAS function would use the values of other Tables as the filter Values of the Current Table.

 

 


 

Super User
Super User

Re: How to display data from two tables (one table created using query editor)

Hi,

Try this:

  1. Create a Calendar Table and build a relationship from the Date column of the 2 Tables to the Date column of the Calendar Table
  2. Create another Table with unique Sales reps and build a relationship from the Sales rep column of the 2 Tables to the Sales rep column of the new Table
  3. To your slicer visual, drag the Sales rep column to the slicer
  4. To the Table visual, drag Date from the Calendar Table
  5. Write these measures

Actual sales = SUM(Sales[Sales])

Target sales = SUM(Target[Sales])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,381)