Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
89 | |
66 |