Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Does this do what you want?
= CALCULATE ( SUMX ( VALUES( Table1[VALUE] ), Table1[VALUE] ), ALLEXCEPT ( Table1, Table1[YEAR], Table1[WEEK], Table1[CAT] ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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
Does this do what you want?
= CALCULATE ( SUMX ( VALUES( Table1[VALUE] ), Table1[VALUE] ), ALLEXCEPT ( Table1, Table1[YEAR], Table1[WEEK], Table1[CAT] ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 ?
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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |