Forum Discussion
Need help for calculating data
I have the following secnario
Table 1 (Targets) :
| SalesMan | Brand Id | Target to achieve | PeriodCode | PeriodStartDate | PeriodEndDate |
| 555 | 11 | 10000 | Oct-18 | 01-10-18 | 31-10-18 |
| 555 | 12 | 12000 | Oct-18 | 01-10-18 | 31-10-18 |
Table 2 (OrderLines) :
| SalesMan | Brand | Item | SalesAmount | OrderDate |
| 555 | 11 | 1000 | 5000 | 14-10-18 |
| 555 | 11 | 1000 | 1000 | 15-10-18 |
| 555 | 11 | 2000 | 1000 | 16-10-18 |
| 555 | 12 | 3000 | 6000 | 17-10-18 |
| 555 | 12 | 4000 | 1000 | 18-10-18 |
I want to add a coulmn to Table 1 as "Actual sales" to calculate the sum of sales amount for each salesman for each brand for each period.
Thanks in Advance.
Hi A_H ,
I would create additional tables to make dimension table to relate this two.
SalesMan = SUMMARIZE(ALL(Targets[SalesMan]);Targets[SalesMan]) Brands = SUMMARIZE(ALL(Targets[Brand Id]);Targets[Brand Id]) PeriodCode = SUMMARIZE(ALL(Targets[PeriodCode]);Targets[PeriodCode])
Also would create an additional column on orderlines to have the period code
PeriodCode = FORMAT(OrderLines[OrderDate ];"mmm-yy")
Be aware that this are DAX formulas but this columns and tables can also be created in the query editor.
Also regarding the period you can also create a calendar table to relate with the other tables.
Then make a realtion ship betwen the 3 new tables and the other 2.
Use the columns on the dimension tables for your data and the rest comes from the other tables (as you can see I added a measure with the difference to target):
Check PBI file attach
Regards,
MFelix
2 Replies
Hi A_H ,
I would create additional tables to make dimension table to relate this two.
SalesMan = SUMMARIZE(ALL(Targets[SalesMan]);Targets[SalesMan]) Brands = SUMMARIZE(ALL(Targets[Brand Id]);Targets[Brand Id]) PeriodCode = SUMMARIZE(ALL(Targets[PeriodCode]);Targets[PeriodCode])
Also would create an additional column on orderlines to have the period code
PeriodCode = FORMAT(OrderLines[OrderDate ];"mmm-yy")
Be aware that this are DAX formulas but this columns and tables can also be created in the query editor.
Also regarding the period you can also create a calendar table to relate with the other tables.
Then make a realtion ship betwen the 3 new tables and the other 2.
Use the columns on the dimension tables for your data and the rest comes from the other tables (as you can see I added a measure with the difference to target):
Check PBI file attach
Regards,
MFelix
