Calculating a percentage based on overall totals

[dawgfather]

I have looked through a number of postings and I'm close but can't get this to work. 

I am looking to be able to calculate a percentage of how much data is shared on a hard drive amongst users and their folders. So the theory is that all of their folder sizes (added together) would be 100% and then each user would show up as a certain % based on the aggregate of their data. I had something in mind based on other postings, but it is not working. Something like this:

= (SUM(ProvisionedSpace)) / (SUM(Total  <UserID> (ProvisionedSpace))

Example data:

UserID	ProvisionedSpace	My calculated %
A	2	0.2%
A	6	0.5%
A	10	0.8%
A	20	1.6%
A	30	2.5%
A	50	4.1%
A	100	8.2%
A	300	24.6%
B	500	41.1%
B	200	16.4%
Totals	1218	100.0%

I added the third column in Excel just to show calculations behind the scenes and we'd only have the first and second columns. 

A's total should be 42.5% if you add all of their folders and their folder percentages together, compared to the total.

B's total should be 57.5% based on the same. 

So in a chart – take a bar chart where you’d have UserID as a Dimension, I'd end up with a 42.5/57.5 split. If a general chart you’d get 2 lines – A = 42.5% and B = 57.5% in an Expression column.

Thoughts?

[sunny_talwar]

Try this

=Sum(ProvisionedSpace)/Sum(TOTAL ProvisionedSpace)

[dplr-rn]

I am assuming you need provisioned space as a dimension.

Use below expression 

=Sum(TOTAL<UserID> ProvisionedSpace)/Sum(TOTAL ProvisionedSpace)

output

[dawgfather]

Ok...so almost there...I had it backwards and missing a "TOTAL"...

So that works for showing a single hard drive...if I do one by one individually I get 100% and then the breakouts...awesome.

But if I have more than one drive, my charts get wonky. I'd expect that each drive shows as their own independent 100% but they don't.

So I guess that 3rd dimension has to come into play...I'll add that to the sample data below to see if that makes a difference and maybe it's just my Dimensions chosen or something.

Each Drive (top Dimension) should have it's own column with divisions by UserID (second Dimension) - with the Expression you provided -->     =Sum(ProvisionedSpace) / Sum(TOTAL ProvisionedSpace)

New data - changed to elaborate:

Drive	UserID	ProvisionedSpace	MyCalculated%	Drive's SUM
1	A	10	4.6%	219
1	A	100	45.7%
1	B	25	11.4%
1	B	84	38.4%
2	A	54	14.9%	363
2	B	68	18.7%
2	C	123	33.9%
2	D	21	5.8%
2	A	97	26.7%
3	A	105	5.9%	1791
3	D	97	5.4%
3	G	900	50.3%
3	G	35	2.0%
3	H	654	36.5%

I have an Axes set to Static Max of "1" which would be 100%.

Right now filtered/selected individually, they show properly at 100%. If I let all 3 show together in a chart, they all go down to less than 30% each.

[dawgfather]

Almost there...could you look at the other reply I had about having multiple Drives - adding another variable throws the calculations off.

[sunny_talwar · Accepted solution]

Then try this

=Sum(ProvisionedSpace) / Sum(TOTAL <Drive> ProvisionedSpace)

[dawgfather]

Perfect. Thank you. The first solution was great for a single item and this last one worked for multiple. Greatly appreciate the quick responses!