Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
Post Prodigy
Post Prodigy

Hi

You can use semi additive measure in this scenerio.

 

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

negi007
Community Champion
Community Champion

@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 on linkedin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.