CODEWITHSUNDEEP
powerbi
Sonali
Sonali

Reputation: 2283

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

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?

Upvotes: 1

Views: 7762

Answers (1)

Leonard
Leonard

Reputation: 2578

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.

enter image description here

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

Upvotes: 2

Related Questions