CODEWITHSUNDEEP
dax
Pooja
Pooja

Reputation: 71

Sum of only Distinct values in a Column in DAX

I have table[Table 1] having three columns OrganizationName, FieldName, Acres having data as follows

organizationname fieldname Acres

ABC |F1 |0.96

ABC |F1 |0.96

ABC |F1 |0.64

I want to calculate the sum of Distinct values of Acres (eg: 0.96+0.64) in DAX.

Upvotes: 1

Views: 7861

Answers (2)

Ryan B.
Ryan B.

Reputation: 3665

Ok, you added these requirements:

Thank You. :) However, I want to add Distinct values of Acres for a Particular Fieldname. Is this possible? – Pooja 3 hours ago

The easiest way really is just to go ahead and slice or filter the original measure that I gave you. But if you have to apply the filter context in DAX, you can do it like this:

Measure = 
SUMX(
    FILTER(
        SUMMARIZE( Table1, [FieldName], [Value] )
        , [FieldName] = "<put the name of your specific field here"
    )
    , [Value]
)

Upvotes: 0

Ryan B.
Ryan B.

Reputation: 3665

One of the problems with doing what you want is that many measures rely on filters and not actual table expressions. So, getting a distinct list of values and then filtering the table by those values, just gives you the whole table back.

The iterator functions are handy and operate on table expressions, so try SUMX

TotalDistinctAcreage = SUMX(DISTINCT(Table1[Acres]),[Acres])

This will generate a table that is one column containing only the distinct values for Acres, and then add them up. Note that this is only looking at the Acres column, so if different fields and organizations had the same acreage -- then that acreage would still only be counted once in this sum.

If instead you want to add up the acreage simply on distinct rows, then just make a small change:

TotalAcreageOnDistinctRows = SUMX(DISTINCT(Table1),[Acres])

Hope it helps.

Upvotes: 1

Related Questions