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

[sajith]

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

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

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]

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.

---