Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |