Hoping someone might be able to tell me if this is possible.
I have 2 examples below and while the first it easier to understand the second one is where I get stuck. Below is a table of 7 sites and each has a Total which is made up of GrpA value + GrpB values.
With those 7 sites we can create a
GrpA %
using = DIVIDE(SUM('Table'[GrpA]),SUM('Table'[Total]))
there is a Nat table similar to the one above and to get the
NatA %
we can use = DIVIDE(SUM('Table (2)'[NatA]),SUM('Table (2)'[NatTotal]))
.
To be able to work out the difference between the GrpA and NatA we could use
A % Var = [GrpA %]-[NatA %]
and while this looks correct if you add the A % Var values together you get 4% instead of 2%
SUMX A Var = SUMX('Table',[A % Var])
will give us the 4% result.
So the second table below has the same values as the first but rather than having the Grp A and B values in separate columns they are combine into 1 column.
So in the second example it looks the same except to get the % we use
A Grp = CALCULATE(SUM('Table'[Units]),FILTER('Table','Table'[Grp]="A"))
and then
GrpA % = DIVIDE([A Grp],[Units])
Like the first example we can use A % Var = [GrpA %]-[NatA %]
which will give us the 2% but when I try
SUMX A Var = SUMX('Table',[A % Var])
to get the 4% all results are 0%. I realize that this is caused by the GrpA & B values being pulled into one column but would it be possible to achieve the same result at the first example to get the 4% total?
Much appreciated if someone could point me the right direction.
I think you need to use SUMX(VALUES(...))
instead of just sumx
.
sumx
simply adds up all the values in a single column while SUMX(VALUES(...))
is useful when you need to calculate something based on distinct values from another column but still want row-by-row context. The VALUES function returns a table of distinct values, and SUMX iterates over that table.