calculate sum of distinct value linked to multiple columns

[ArBohe]

Hi - I have a spotfire background where doing a sum(distinct(x)) is a no brainer.

it seems I do not get the logic nor the syntax to do the same in power BI...

Given this table:

YEAR WEEK CAT CODT VALUE
2017 1 cat1 A 12
2017 1 cat1 B 12
2017 1 cat2 A 23
2017 1 cat2 B 23
2017 1 cat3 A 44
2017 1 cat3 B 44
2017 2 cat1 A 39
2017 2 cat1 B 39
2017 2 cat2 A 25
2017 2 cat2 B 25
2017 2 cat3 A 11
2017 2 cat3 B 11
2018 1 cat1 A 15
2018 1 cat1 B 15
2018 1 cat2 A 22
2018 1 cat2 B 22
2018 1 cat3 A 16
2018 1 cat3 B 16
2018 2 cat1 A 19
2018 2 cat1 B 19
2018 2 cat2 A 42
2018 2 cat2 B 42
2018 2 cat3 A 14
2018 2 cat3 B 14

I need to add an additional column (calculated) which SUM the DISTINCT values grouped by year, week, cat.

for instance for a line 2017, 1, Cat1,  I should see 12

I'm sure it's trivial, but I'm totally lost.

Any help welcome!!!

Arnaud

[LivioLanzo · Accepted solution]

@ArBohe

Does this do what you want?

= 
CALCULATE (
    SUMX ( VALUES( Table1[VALUE] ), Table1[VALUE] ),
    ALLEXCEPT ( Table1, Table1[YEAR], Table1[WEEK], Table1[CAT] )
)

[ArBohe]

Hi - I have a spotfire background where doing a sum(distinct(x)) is a no brainer.

it seems I do not get the logic nor the syntax to do the same in power BI...

Given this table:

YEAR WEEK CAT CODT VALUE
2017 1 cat1 A 12
2017 1 cat1 B 12
2017 1 cat2 A 23
2017 1 cat2 B 23
2017 1 cat3 A 44
2017 1 cat3 B 44
2017 2 cat1 A 39
2017 2 cat1 B 39
2017 2 cat2 A 25
2017 2 cat2 B 25
2017 2 cat3 A 11
2017 2 cat3 B 11
2018 1 cat1 A 15
2018 1 cat1 B 15
2018 1 cat2 A 22
2018 1 cat2 B 22
2018 1 cat3 A 16
2018 1 cat3 B 16
2018 2 cat1 A 19
2018 2 cat1 B 19
2018 2 cat2 A 42
2018 2 cat2 B 42
2018 2 cat3 A 14
2018 2 cat3 B 14

I need to add an additional column (calculated) which SUM the DISTINCT values grouped by year, week, cat.

for instance for a line 2017, 1, Cat1,  I should see 12

I'm sure it's trivial, but I'm totally lost.

Any help welcome!!!

Arnaud

[LivioLanzo · Accepted solution]

@ArBohe

Does this do what you want?

= 
CALCULATE (
    SUMX ( VALUES( Table1[VALUE] ), Table1[VALUE] ),
    ALLEXCEPT ( Table1, Table1[YEAR], Table1[WEEK], Table1[CAT] )
)

[ArBohe]

that's perfect!!! Now on to the explanation 🙂

I understand the calculate and the allexcept (which seems to me reversed: it should be 'onlyfor' not 'all except' !!!

However, I do not get the sumx. why not just a sum ?

[LivioLanzo · Accepted solution]

@ArBohe

VALUES will return a 1 column table of distinct values of the column 'VALUE' and then we're iterating this table to sum these distinct values