issue with Matrix groups sub totals

[Vedvati]

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...

[amitchandak]

 Where is " bonus applicable " flag.

[Vedvati]

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.

[amitchandak]

We are getting the number shown in the table. What is the expected number?

[Vedvati]

I am getting double value. Calculated Bonus and Final Bonus are Custom and Conditional Columns respectively.

[Vedvati]

Hi,

Kindly have look into my report.

[v-lili6-msft · Accepted 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

[Vedvati]

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.