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.
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êsCovering 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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |