Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi,
Try this:
Actual sales = SUM(Sales[Sales])
Target sales = SUM(Target[Sales])
Hope this helps.
Hi,
Try this:
Actual sales = SUM(Sales[Sales])
Target sales = SUM(Target[Sales])
Hope this helps.
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.
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |