Forum Discussion
Helper INewbie - matrix view sub totals is adding % vertically rather than calculating across
Hi all,
Complete newbie here - i have a sales data set uploaded directly from excel. I have added in columns with calculations to show growth. However when i add sub totals into my matrix view - it's summing up the individual % for each sales line whereas i want it to base the growth on the total.
Example below - the correct calc should be -31% but the sub totals is adding the two % together to get a growth of 16%
| Actual | Budget | Correct Calc | Power BI Calc | ||
| Carrots | 1000 | 650 | 54% | 54% | |
| Pears | 5000 | 8000 | -38% | -38% | |
| 6000 | 8650 | -31% | 16% |
6 Replies
- ToddChitt
Super User
Hello EvaHello and welcome to the Power BI / Fabric community.
Let's assume that the correct formula to calculate the percentage is:
( [Actual] - [Budget] ) / [Budget]
( 1000 - 650 ) / 650
350 / 650 = 54%
If you make that calculation on each ROW then you get the correct number.
What you need is a MEASURE that acts on the Aggregation of the columns in the formula. At the summary level, that would look like this:
( 6000 - 8650 ) / 8650 = -31%
So the definition of the MEASURE needs to be something like this:
My Correct Calc = ( SUM([Actual]) - SUM([Budget]) ) / SUM(Budget)
When that measure is evaluated at the individual ROW level, the calculation still holds true, because it is calculating summaries of one row only.
Hope that helps.
- Ashish_Mathur
Hi,
Share the download link of the PBi file.
Hi EvaHello
Thank you for reaching out microsoft fabric community forum.
Is this expected ouput ?I have attached the sample PBIX file. Please feel free to reach out to us for further assistance if required.
If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community.
Thank you.
Hi EvaHello
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi EvaHello
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi EvaHello
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
