powerbidax

SUMX with filter with a measure

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.

table 1

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.

example1

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.

table2

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?

example 2

Much appreciated if someone could point me the right direction.

Solution

  • 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.