Forum Discussion
Helper IVHelp explaining % of row total formula
Hi all,
I'm a new user to PBI and a colleague of mine was asking for some help on calculating the % of Row Total in a bar chart, which wasn't being presented to him as an option in the list of "show value as". We came across this formula online:
Measure = CALCULATE(SUM(Sheet1[ID]))/CALCULATE(SUM(Sheet1[ID]),ALL(Sheet1[Status]))
This makes it work beautifully, but for the life of my I can't work out why. Would someone be so kind as to explain it in laymens terms? Why would I divide the total by the total? Thankyou!
Matt
Two part of this, first part CALCULATE(SUM(Sheet1[ID])) calculates the sum based on fitler context and
2nd part CALCULATE(SUM(Sheet1[ID]),ALL(Sheet1[Status])) sum of all the values and ALL function remove any filters
which allos to get total
Assume you have two columns Category and Sales
A 100
A 100
B 200
B 200
C 400
Total sales is = 100 + 100 + 200 + 200 + 400 = 1000
Assume you have category on X axis and % formula on values to show the bar
So when it is showing % for category A , it is SUM of sales of category A which is 100 + 100 = 200 and 2nd part
is doing total sales which is 1000 and % would be 200 / 1000
and when it is category B, sum of category B is 200 + 200 = 400 and % will be 400 / 1000 , so on...
hope it helps.
1 Reply
- parry2k
Super User
Two part of this, first part CALCULATE(SUM(Sheet1[ID])) calculates the sum based on fitler context and
2nd part CALCULATE(SUM(Sheet1[ID]),ALL(Sheet1[Status])) sum of all the values and ALL function remove any filters
which allos to get total
Assume you have two columns Category and Sales
A 100
A 100
B 200
B 200
C 400
Total sales is = 100 + 100 + 200 + 200 + 400 = 1000
Assume you have category on X axis and % formula on values to show the bar
So when it is showing % for category A , it is SUM of sales of category A which is 100 + 100 = 200 and 2nd part
is doing total sales which is 1000 and % would be 200 / 1000
and when it is category B, sum of category B is 200 + 200 = 400 and % will be 400 / 1000 , so on...
hope it helps.