Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Then try this
=Sum(ProvisionedSpace) / Sum(TOTAL <Drive> ProvisionedSpace)
Try this
=Sum(ProvisionedSpace)/Sum(TOTAL ProvisionedSpace)
I am assuming you need provisioned space as a dimension.
Use below expression
=Sum(TOTAL<UserID> ProvisionedSpace)/Sum(TOTAL ProvisionedSpace)
output
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.
Almost there...could you look at the other reply I had about having multiple Drives - adding another variable throws the calculations off.
Then try this
=Sum(ProvisionedSpace) / Sum(TOTAL <Drive> ProvisionedSpace)
Perfect. Thank you. The first solution was great for a single item and this last one worked for multiple. Greatly appreciate the quick responses!