cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mb0307
Responsive Resident
Responsive Resident

Percentage sum by category

Hi all,

 

How can acheive correct SUM %  please?  I have attahed PBIX file with data and example:

 

https://www.dropbox.com/s/zwtz1dc5qbfppmi/Percentage%20by%20category.pbix?dl=0 

 

Result.jpg

 

Thanks you very much.

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@mb0307 here is the measure that will do it:

 

Percentage Diff = 
SUMX ( 'Product Group', DIVIDE (  ABS ( [Total Forecast] - [Total Sale] ), [Total Sale], 0 ) )

Sum Diff = SUMX ( VALUES ('Calendar'[Month Name] ), [Percentage Diff] )

 

and use Sum Diff measure in the matrix visual and here is the output

 

parry2k_0-1638634937044.png

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@mb0307 percentage diff will not have the correct grand total. 

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@mb0307 here is the measure that will do it:

 

Percentage Diff = 
SUMX ( 'Product Group', DIVIDE (  ABS ( [Total Forecast] - [Total Sale] ), [Total Sale], 0 ) )

Sum Diff = SUMX ( VALUES ('Calendar'[Month Name] ), [Percentage Diff] )

 

and use Sum Diff measure in the matrix visual and here is the output

 

parry2k_0-1638634937044.png

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





mb0307
Responsive Resident
Responsive Resident

@parry2k it worked.  Thanks a lot. One question:

 

Percentage Diff = 
SUMX ( 'Product Group', DIVIDE (  ABS ( [Total Forecast] - [Total Sale] ), [Total Sale], 0 ) )

 

shows the same result as Sum Diff then why to use Sum Diff??

 

Sum Diff = SUMX ( VALUES ('Calendar'[Month Name] ), [Percentage Diff] )

 

Thanks again

rajendraongole1
Continued Contributor
Continued Contributor

Hi

You can use semi additive measure in this scenerio.

 

Like Lastdate() or firstdate as per the given values.

negi007
Super User
Super User

@mb0307 but output is correct as per the formula used by you then why you want something else as output. can you tell us what formula you want for your output




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors