I'm just starting to familarize myself with Powerpivot and DAX formulas and I have a feeling it would be ideal for what I'm trying to do; I just can't figure it out. I'm looking for two things:
1. Calculate a % of total within certain groupings. So for instance, my table may look something like this:
<tbody>
</tbody>
So what I need this formula to do is calculate the % of total based on the pairing of Group 2 and 3 but within Group 1. For instance,
Red > Light > A should be 100 / (100 + (-50) + 200) = 40%
Red > Medium > B should be - 50 / (100 + (-50) + 200) = -20%
There is one additional step and that is that I need to calculate based on absolute values. So my examples above would turn into:
Red > Light > A should be 100 / (100 + 50 + 200) = 29%
Red > Medium > B should be -50 / (100 + 50 + 200) = 14%
So that is formula # 1 I need.
The second one that I need is to calculate the average but also be able to calculate a trimmed average. It needs to be based on the combination of all 3 groupings (my source data will have many more rows). But essentially I would need it to calculate the average of all rows where 1 = Red, 2 = Medium, and 3 = B ... and to be able to average only if the values are within a set range. Otherwise, just ignore them.
I could do all of this in an Excel table but is this the sort of thing Powerpivot would help with?
1. Calculate a % of total within certain groupings. So for instance, my table may look something like this:
Group 1 | Group 2 | Group 3 | Value | |
Red | Light | A | 100 | |
Red | Medium | B | -50 | |
Yellow | Dark | C | 400 | |
Orange | Medium | B | 500 | |
Red | Light | C | 200 |
<tbody>
</tbody>
So what I need this formula to do is calculate the % of total based on the pairing of Group 2 and 3 but within Group 1. For instance,
Red > Light > A should be 100 / (100 + (-50) + 200) = 40%
Red > Medium > B should be - 50 / (100 + (-50) + 200) = -20%
There is one additional step and that is that I need to calculate based on absolute values. So my examples above would turn into:
Red > Light > A should be 100 / (100 + 50 + 200) = 29%
Red > Medium > B should be -50 / (100 + 50 + 200) = 14%
So that is formula # 1 I need.
The second one that I need is to calculate the average but also be able to calculate a trimmed average. It needs to be based on the combination of all 3 groupings (my source data will have many more rows). But essentially I would need it to calculate the average of all rows where 1 = Red, 2 = Medium, and 3 = B ... and to be able to average only if the values are within a set range. Otherwise, just ignore them.
I could do all of this in an Excel table but is this the sort of thing Powerpivot would help with?