Power Bi: Calculate sum of column value with distinct other column values

Question

in power bi I have following table, I want to calculate the total keywords from the table below. Ids are replicating because they are coming from some other Stored Procedure. I want sum to be like this 12+13+14.

Id  Keywords
A   12
B   13
B   13
B   13
B   13
C   14
C   14
B   13
A   12
C   14
C   14
C   14
C   14
A   12
A   12
B   13
A   12
B   13
A   12
B   13

I tried X = SUMX(FILTER(Query1,DISTINCT(Query1[Id])),[Keywords]) and X = CALCULATE(SUM(Query1[Keywords]), FILTER(Query1, DISTINCT(Query1[Id]))) But nothing is coming.

How this can be done?

Leonard · Accepted Answer · 2017-07-10 17:37:41Z

2

Try, for example:

X =
SUMX ( SUMMARIZE ( Query1, Query1[Id], Query1[Keywords] ), [Keywords] )

This relies on a 1:1 relationship between Id & Keywords. (If A could sometimes be 12, and sometimes be 13, the above will include both A-12 and A-13.)

Modifying your query to only bring in a distinct set would be ideal, rather than using DAX to de-dupe after the fact. If you're using Power Query, for example, you can remove duplicates as the data is being imported even though your stored procedure is sending them (and simplify your DAX expressions). From your canvas, Home > Edit Queries > Edit Queries. Then select the last step (in my screenshot it's Changed Type) and choose, Home > Remove Rows > Remove Duplicates.

Or ideally, update the stored procedure to not send duplicates (although I realize that might be out of your control).

answered Jul 10, 2017 at 17:37

Leonard

2,5962 gold badges17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sonali Over a year ago

I know it will be out of my question's scope, but can you explain how this works?? (SUMX & SUMMARIZE)

Sonali Over a year ago

Can you please help me with this stackoverflow.com/questions/44884315/…

Leonard Over a year ago

SUMMARIZE is similar to a GROUP BY clause in SQL. In this example, it says "make a table from Query1 with only the unique combinations (distinct values) of Id & Keywords". The SUMX says "sum the Keywords column from the SUMMARIZE table. SUMX is necessary because I want to specify what table the column Keywords is in: my SUMMARIZE table, not the Query1 table. More information about SUMMARIZE: msdn.microsoft.com/en-us/library/gg492171.aspx

Collectives™ on Stack Overflow

Power Bi: Calculate sum of column value with distinct other column values

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related