Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
A_H
Frequent Visitor

Need help for calculating data

I have the following secnario  


Table 1 (Targets) :

SalesManBrand IdTarget to achievePeriodCodePeriodStartDatePeriodEndDate
5551110000Oct-1801-10-1831-10-18
5551212000Oct-1801-10-1831-10-18





Table 2 (OrderLines) : 

SalesManBrandItemSalesAmountOrderDate 
555111000500014-10-18
555111000100015-10-18
555112000100016-10-18
555123000600017-10-18
555124000100018-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. 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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.

ORderlines.png

 

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):

 

salesordertable.png

 

Check PBI file attach

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

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.

ORderlines.png

 

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):

 

salesordertable.png

 

Check PBI file attach

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



A_H
Frequent Visitor

Thanks @MFelix  it is worked 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.