Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello, everyone,
I have the following request.
I have sales (Sales Invoice Line) per salesperson.
Salesperson has specific goals (Target). Such as Amount in a specific city or Amount to a specific customer.
I would like to maintain the targets for each salesperson in the target table, including the DAX expression.
Is it possible to integrate the constant DAX expression from Excel (Column "DAX Filter" in "Target" table) into Power BI?
So that I don't have to create a measure per city, but only one measure "Amount City"
Something like this:
New Measure:
Amount City = [DAX Filter]
My data model:
Output:
Sample Data:
Target
Code | Type | Plan | DAX Filter | Planned | Year |
JR | Amount with Customer + DAX | Amount in City | CALCULATE( [Sum of Amount] , FILTER('Sales Invoice Line' , 'Sales Invoice Line'[City] = "München" ) ) | 10000 | 2010 |
AH | Amount with Customer + DAX | Amount in City | CALCULATE( [Sum of Amount] , FILTER('Sales Invoice Line' , 'Sales Invoice Line'[City] = "Frankfurt a. M." ) ) | 200 | 2010 |
PS | Amount with Customer + DAX | Amount with Customer | CALCULATE( [Sum of Amount] , FILTER('Sales Invoice Line' , 'Sales Invoice Line'[Sell-to Customer No_] = "40000" ) ) | 1500 | 2010 |
AH | Count of | Count of Order in Suisse | 25 | 2010 | |
JR | Count of | Count of Order in Austria | 20 | 2010 | |
JR | Count of | Count of Customers in Austria | 50 | 2010 |
Sales Invoice Line
Document No_ | Line No_ | Sell-to Customer No_ | No_ | Amount | Quantity | Salesperson Code | Posting Date | City |
103019 | 10000 | 40000 | 8908-W | 530,4 | 3 | PS | 13.12.2010 | Berlin |
103019 | 20000 | 40000 | 8916-W | 579,6 | 2 | PS | 13.12.2010 | Berlin |
103019 | 30000 | 40000 | 8924-W | 536,3 | 1 | PS | 13.12.2010 | Berlin |
VRG1001168 | 40000 | 113000 | 1996-S | 2780,46 | 2 | JR | 07.06.2010 | München |
VRG1001328 | 20000 | 113000 | 1928-W | 1049,18 | 2 | JR | 28.06.2010 | München |
VRG1000746 | 20000 | 113000 | 1960-S | 383,5 | 2 | JR | 12.04.2010 | München |
VRG1001382 | 10000 | 113000 | 1928-W | 1049,18 | 2 | JR | 05.07.2010 | München |
VRG1001434 | 70000 | 113000 | 1984-W | 1494,78 | 1 | JR | 12.07.2010 | München |
VRG1001434 | 60000 | 113000 | 1980-S | 189,11 | 1 | JR | 12.07.2010 | München |
VRG1002255 | 30000 | 129000 | 1968-W | 1494,78 | 1 | JR | 01.11.2010 | München |
VRG1002255 | 20000 | 129000 | 1960-S | 191,75 | 1 | JR | 01.11.2010 | München |
VRG1002497 | 20000 | 129000 | 1928-S | 54,52 | 1 | JR | 06.12.2010 | München |
VRG1002255 | 10000 | 129000 | 1896-S | 995,74 | 1 | JR | 01.11.2010 | München |
VRG1002497 | 10000 | 129000 | 1908-S | 189,11 | 1 | JR | 06.12.2010 | München |
VRG1002498 | 10000 | 127000 | 1908-S | 234 | 3 | GD | 06.12.2009 | Stuttgart |
VRG1008498 | 10000 | 127000 | 1908-W | 289,13 | 2 | VR | 06.11.2009 | Stuttgart |
VRG1002454 | 10000 | 125000 | 1908-S | 234 | 3 | AH | 06.12.2010 | München |
VRG1001455 | 10000 | 114000 | 1980-S | 169,11 | 1 | JR | 12.07.2010 | Heilbronn |
VRG1002219 | 10000 | 12200 | 1968-W | 1794,78 | 1 | JR | 01.11.2010 | Heilbronn |
VRG1002875 | 10000 | 129000 | 1908-S | 289 | 4 | AH | 06.10.2010 | Frankfurt a. M. |
I thank you in advance!
Hi, @denxx34
According to the data you have provided, If there are not many types of cities, it is recommended to write measures separately, because your data is more complex.
E.g: I see that a sales person in your data may sell more than one city, how to deal with this situation?
Best Regards,
Community Support Team _Janey
@denxx34 , You need to have target table, salesperson, city and date(month/year etc) and that need join with column tables like salesperson, city, and date. same is true sales invoice table
Thanks for your answer, but I don't quite understand what you mean. Can you please explain it in a little more detail?
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
6 |