cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vedvati
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 NoProductProduct AmountBonus AmountResourceBonus Percentage
WO000072Service Charge100010Jim Bowen1%
WO00007215K Greese15000Jim Bowen1%
WO000072Service Charge100050Rob Milton5%
WO00007215K Greese15000Rob Milton5%

 

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

View solution in original post

7 REPLIES 7
Super User IV
Super User IV

Re: issue with Matrix groups sub totals

 Where is " bonus applicable " flag.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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


Vedvati
Frequent Visitor

Re: issue with Matrix groups sub totals

Hi,

Following is the table.

Wo. No.Product NameProduct AmountResource WorkingBonus StatusBonus PercentCalculted Bonus(Product Amount*Bonus Percent/100)Final Bonus(If Bonus Status is True)
WO0001Service Charge15000RobTrue1150150
WO00015 K PCE3000NickTrue13030
WO0001Service Charge15000NickFalse11500
WO00015 K PCE3000RobTrue26060
WO0001Discount0NickTrue100

 

I want to show this in Matrix Format.

I will be showing 2 Matrix.

1. Total Matrix

    This Matrix will show

Wo.No.Product TotalBonus Total% Revenue
WO0001360002400.67%

 

2. Bonus By Resources Matrix

    This Matrix will show

Wo.No.ProductRobNick
WO0001Service Charge1500
 5 K PCE6030
 Discount 0
Total 21030

 

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
Super User IV

Re: issue with Matrix groups sub totals

We are getting the number shown in the table. What is the expected number?Screenshot 2020-01-23 14.19.18.png

 

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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


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

Vedvati
Frequent Visitor

Re: issue with Matrix groups sub totals

Hi,

Kindly have look into my report.

 

Matrix Table.PNG

 

Matrix View.PNG

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

View solution in original post

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

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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