Forum Discussion
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
Hi,
Try this:
- Create a Calendar Table and build a relationship from the Date column of the 2 Tables to the Date column of the Calendar Table
- 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
- To your slicer visual, drag the Sales rep column to the slicer
- To the Table visual, drag Date from the Calendar Table
- Write these measures
Actual sales = SUM(Sales[Sales])
Target sales = SUM(Target[Sales])
Hope this helps.
3 Replies
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.
Thanks for the reply. will check revert soon.
Anonymous 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.
Hi,
Try this:
- Create a Calendar Table and build a relationship from the Date column of the 2 Tables to the Date column of the Calendar Table
- 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
- To your slicer visual, drag the Sales rep column to the slicer
- To the Table visual, drag Date from the Calendar Table
- Write these measures
Actual sales = SUM(Sales[Sales])
Target sales = SUM(Target[Sales])
Hope this helps.
