- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Sum of distinct values.

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

VLRE

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-15-2019
04:03 PM

Hi, I have a table which I am trying to process to find out the sum of Input Values for distinct Linked Permit numbers.

I can't delete duplicates, because this is a part of other columns, where I need duplicates.

I am trying to do this and it does not work, how hard I tried.

Sum of all input values = sum(TEST[Input Value])

Distinct sum = sumx(DISTINCT(TEST[Linked Permit]),[Sum of all input values])

Logic tells me that DISTINCT should create a context were only distinct Linked Permits exist, and then SUMX should add all Input Values, but it does not work. Can you please help? I searched everywhere and no help...

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

AlB

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-15-2019
05:06 PM

Do you mean the version with DISTINCT should sum only once the duplicate rows? When you talk about duplicates, is it the whole row (i.e. all columns) that are duplicated?

If all rows are duplicated, wouldn't the result you want just what you get now divided by two?

Try these measures, a variation of what you had:

Sum of all input values = sum(TEST[Input Value])

Number Of Duplicates = COUNTROWS(TEST)

Distinct sum = SUMX ( DISTINCT ( TEST[Linked Permit] ), DIVIDE ( [Sum of all input values], [Number Of Duplicates] ) )

3 REPLIES 3

AlB

Super User

Re: Sum of distinct values.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-15-2019
04:31 PM

Hi @VLRE

What is the problem exactly? What is the result as opposed to your expected/required result?

VLRE

Frequent Visitor

Re: Sum of distinct values.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-15-2019
04:41 PM

The issue is that the result of those measures is literally the same, so no filtering is going on in regards of distinct values (Linked Permit). I think it's visible on the picture above.

AlB

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-15-2019
05:06 PM

Do you mean the version with DISTINCT should sum only once the duplicate rows? When you talk about duplicates, is it the whole row (i.e. all columns) that are duplicated?

If all rows are duplicated, wouldn't the result you want just what you get now divided by two?

Try these measures, a variation of what you had:

Sum of all input values = sum(TEST[Input Value])

Number Of Duplicates = COUNTROWS(TEST)

Distinct sum = SUMX ( DISTINCT ( TEST[Linked Permit] ), DIVIDE ( [Sum of all input values], [Number Of Duplicates] ) )