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.
Hey Guys,
I have an issue with the Power Bi Report,
Below is the Table Matrix. We have Work Orders, then in the next column we have the Work order line items, Amount of the line items, The resources that worked on that line item and the bonus % the resources should get on the Product amount, and the bonus amount.
Work Order No | Product | Product Amount | Bonus Amount | Resource | Bonus Percentage |
WO000072 | Service Charge | 1000 | 10 | Jim Bowen | 1% |
WO000072 | 15K Greese | 1500 | 0 | Jim Bowen | 1% |
WO000072 | Service Charge | 1000 | 50 | Rob Milton | 5% |
WO000072 | 15K Greese | 1500 | 0 | Rob Milton | 5% |
In the above scenario, there are 2 resource Jim and Rob working on 2 line items, Jim gets 1% bonus and Rob get 5 % bonus.
The second line item i.e 15k Greese should not get bonus as the bonus applicable is not checked. In the report, in the Bonus Amount Column the data shown is correct. So accordingly, We should get the Total for the Bonus Amount as 60. but the total for that work order shows 120 instead of 60.
Need Help...
Solved! Go to Solution.
hi @Vedvati
This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
For your case, you could try this formula
Measure 2 = SUMX(FILTER('Table',[Bonus Status]=True()),[Product Amount]*[Bonus Percentage])
Regards,
Lin
Where is " bonus applicable " flag.
Hi,
Following is the table.
Wo. No. | Product Name | Product Amount | Resource Working | Bonus Status | Bonus Percent | Calculted Bonus(Product Amount*Bonus Percent/100) | Final Bonus(If Bonus Status is True) |
WO0001 | Service Charge | 15000 | Rob | True | 1 | 150 | 150 |
WO0001 | 5 K PCE | 3000 | Nick | True | 1 | 30 | 30 |
WO0001 | Service Charge | 15000 | Nick | False | 1 | 150 | 0 |
WO0001 | 5 K PCE | 3000 | Rob | True | 2 | 60 | 60 |
WO0001 | Discount | 0 | Nick | True | 1 | 0 | 0 |
I want to show this in Matrix Format.
I will be showing 2 Matrix.
1. Total Matrix
This Matrix will show
Wo.No. | Product Total | Bonus Total | % Revenue |
WO0001 | 36000 | 240 | 0.67% |
2. Bonus By Resources Matrix
This Matrix will show
Wo.No. | Product | Rob | Nick |
WO0001 | Service Charge | 150 | 0 |
5 K PCE | 60 | 30 | |
Discount | 0 | ||
Total | 210 | 30 |
I need to show like this. Kindly help me with this.
I am getting Bonus Amount rihgt in query table but showing double the original value in matrix as 2 resources are working on it.
Need urgent help. Thank you.
We are getting the number shown in the table. What is the expected number?
I am getting double value. Calculated Bonus and Final Bonus are Custom and Conditional Columns respectively.
Hi,
Kindly have look into my report.
hi @Vedvati
This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
For your case, you could try this formula
Measure 2 = SUMX(FILTER('Table',[Bonus Status]=True()),[Product Amount]*[Bonus Percentage])
Regards,
Lin
Thank you for your response. I have one more doubt.
I have many Products with the same name in one work order. Right now it is getting clubbed.
How to show it separately.
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |