cancel
Showing results for
Did you mean:
Responsive Resident

## Percentage sum by category

Hi all,

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

Thanks you very much.

1 ACCEPTED SOLUTION
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

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.

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

5 REPLIES 5
Super User

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

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.

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

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

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.

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

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

Continued Contributor

Hi

You can use semi additive measure in this scenerio.

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

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

Proud to be a Super User!