Forum Discussion
Calculating aggregations/percentages within the same table
Hello all,
I am a new user of power bi and seeking help with some calculations. I have a table with with hierarchy and I am looking for percentage calculations that should change as per my selection, the table is
| Region | Country | School | Campus | Year1 | Year2 |
| Asia | Vietnam | School1 | Campus1.1 | 146 | 84 |
| Asia | Thailand | School2 | Campus2.1 | 213 | 39 |
| Asia | Singapore | School3 | Campus3.1 | 97 | 40 |
| Latam | Brazil | School4 | Campus4.1 | 194 | 41 |
| Europe | Spain | School5 | Campus5.1 | 430 | 69 |
| Europe | UK | School6 | Campus6.1 | 76 | 32 |
| Europe | UK | School6 | Campus6.2 | 63 | 16 |
| Europe | UK | School6 | Campus6.3 | 330 | 53 |
So I am to calculate percentage of change from Year1 to Year 2, I created a column and which results as
| Region | Country | School | Campus | Year1 | Year2 | Percentage(Year1/Year2) |
| Asia | Vietnam | School1 | Campus1.1 | 146 | 84 | 57.5 |
| Asia | Thailand | School2 | Campus2.1 | 213 | 39 | 18 |
| Asia | Singapore | School3 | Campus3.1 | 97 | 40 | 41 |
| Latam | Brazil | School4 | Campus4.1 | 194 | 41 | 21 |
| Europe | Spain | School5 | Campus5.1 | 430 | 69 | 16 |
| Europe | UK | School6 | Campus6.1 | 76 | 32 | 42 |
| Europe | UK | School6 | Campus6.2 | 146 | 84 | 57.5 |
| Europe | UK | School6 | Campus6.3 | 213 | 39 | 18 |
When I try to show the above in a visual, campus level works fine however I would like the 'percentage' to work for school level too.
Could you please help with this?
Best Regards,
TTh
Hi TejaThammu15 ,
Please create the measure:
Measure = DIVIDE ( SUM ( 'Table'[Year2] ), SUM ( 'Table'[Year1] ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-kkf-msftCommunity Support
Hi TejaThammu15 ,
Please create the measure:
Measure = DIVIDE ( SUM ( 'Table'[Year2] ), SUM ( 'Table'[Year1] ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.HI v-kkf-msft
Thanks for your message, It is what I needed and i did some changes as per my requirement. Thank you very much for your help 🙂