CODEWITHSUNDEEP
powerbidax
Belgin Fish
Belgin Fish

Reputation: 19837

Sum distinct values for first occurance in Power BI

In Power BI I have some duplicate entries in my data that only have 1 column that is different, this is a "details" column.

Name   | Value | Details
Item 1 | 10    | Feature 1
Item 1 | 10    | Feature 2
Item 2 | 15    | Feature 1
Item 3 | 7     | Feature 1
Item 3 | 7     | Feature 2
Item 3 | 7     | Feature 3

I realize this is an issue with the data structure, but it cannot be changed.

Basically, when I sum up my Value column on a Power BI card, I only want it to sum for each unique name, so in this case:

Total = 10 + 15 + 7

I will be using the details in a matrix, so I cannot simply remove the duplicates from within the Query Editor.

Is there any way I can filter this with a DAX formula? Just summing the first occurrence of an item?

Upvotes: 5

Views: 52359

Answers (2)

Vishal Gupta
Vishal Gupta

Reputation: 1

This must help

Table = SUMMARIZE(Sheet2,Sheet2[Item],"Sales Quantity",SUM(Sheet2[Sales Quantiy]),"Purchase Quantity",CALCULATE(SUMX(DISTINCT(Sheet2[Purchase Quantity]),FIRSTNONBLANK(Sheet2[Purchase Quantity],0))))

Upvotes: 0

Foxan Ng
Foxan Ng

Reputation: 7151

You can create a measure as follows:

Total = SUMX(DISTINCT(Data[Name]), FIRSTNONBLANK(Data[Value], 0))

It will return the first non-blank Value for all distinct Name and sum it up.

Results:

results

Upvotes: 24

Related Questions