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
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!