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.
Hi Team
I am new to Power BI and DAX, and not sure what would be the best approach to get the below results. Any help or ideas would be much appreciated. Thank you
Tabel1 - SalesTarget
SalesPersonID | MonthNum | Year | Target |
1 | 1 | 2020 | 20000 |
2 | 1 | 2020 | 50000 |
1 | 2 | 2020 | 20000 |
2 | 2 | 2020 | 50000 |
Table 2 - Transactions
SalesPersonID | TransferMonthNum | TransferMonthYear | Actual [calculated field] |
1 | 1 | 2020 | 5000 |
1 | 1 | 2020 | 2000 |
1 | 1 | 2020 | 3000 |
2 | 1 | 2020 | 5000 |
2 | 1 | 2020 | 2000 |
2 | 1 | 2020 | 3000 |
The end results should be
SalesPersID | Month | Year | Actual | Target |
1 | 1 | 2020 | 10000 | 20000 |
2 | 1 | 2020 | 10000 | 50000 |
Solved! Go to Solution.
Hi @robinlolo ,
Create 2 measures as below:
Actual = SUMX(FILTER(ALL(Transactions),'Transactions'[SalesPersonID]=MAX('Transactions'[SalesPersonID])&&'Transactions'[TransferMonthNum]=MAX('Transactions'[TransferMonthNum])),'Transactions'[Actual([calculated field]])])
_Target = LOOKUPVALUE('SalesTarget'[Target],'SalesTarget'[SalesPersonID],MAX('Transactions'[SalesPersonID]),'SalesTarget'[MonthNum],MAX('Transactions'[TransferMonthNum]))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @robinlolo ,
Create 2 measures as below:
Actual = SUMX(FILTER(ALL(Transactions),'Transactions'[SalesPersonID]=MAX('Transactions'[SalesPersonID])&&'Transactions'[TransferMonthNum]=MAX('Transactions'[TransferMonthNum])),'Transactions'[Actual([calculated field]])])
_Target = LOOKUPVALUE('SalesTarget'[Target],'SalesTarget'[SalesPersonID],MAX('Transactions'[SalesPersonID]),'SalesTarget'[MonthNum],MAX('Transactions'[TransferMonthNum]))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@robinlolo , Have common dimensions for Sales Person and Month year. Then you can use measures like these with common table
sum(Sales[Amount])
Sum(Transaction[Target])
Thank you.
Can you shed more light, please?
Covering 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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |