cancel
Showing results for
Did you mean:
Frequent Visitor

## issue with Matrix groups sub totals

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: issue with Matrix groups sub totals

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Super User IV

## Re: issue with Matrix groups sub totals

Where is " bonus applicable " flag.

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Frequent Visitor

## Re: issue with Matrix groups sub totals

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.

Super User IV

## Re: issue with Matrix groups sub totals

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

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Frequent Visitor

## Re: issue with Matrix groups sub totals

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

Frequent Visitor

## Re: issue with Matrix groups sub totals

Hi,

Kindly have look into my report.

Community Support

## Re: issue with Matrix groups sub totals

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: issue with Matrix groups sub totals

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.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors