- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- 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
- Average based on distinct values in another column

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

Highlighted

melz63017

Frequent Visitor

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

08-18-2016
09:37 AM

I have created a Table Visualization that counts the distinct values for a given unique id. I would like the average of the number of distinct values per unique id.

This is what my raw data looks like.

Unique ID | Value |

1 | A |

1 | B |

2 | A |

2 | A |

3 | C |

And the table visualization I want would look like this. Right now the table is only visualizing the Total Number of unique ids.

Unique ID | Distinct Values |

1 | 2 |

2 | 1 |

3 | 1 |

Average | 1.333333 |

Is there a way to get the average from the table visualization? Or should I create a new measure?

Thanks!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

KGrice

Established Member

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

08-18-2016
12:37 PM

Hi @melz63017. You can get the average to display in the totals row:

To get the Distinct Values measure, I started with a couple of building blocks, shown above as the first two measures.

DistinctCountValue = DISTINCTCOUNT(TableName[Value])

DistinctCountUniqueID = DISTINCTCOUNT(TableName[Unique ID])

The last measure in the table is the Distinct Values measure, which uses both of the building blocks defined above:

Distinct Values = IF( HASONEVALUE(TableName[Unique ID]),

[DistinctCountValue],

SUMX(VALUES(TableName[Unique ID]), [DistinctCountValue]) / [DistinctCountUniqueID]

)

The HASONEVALUE function at the beginning of the IF statement checks if your current evaluation context has more than one distinct Unique ID value. So on the table rows that show one Unique ID each, there's one value. For a totals row, there would be multiple values (as long as you don't have your table filtered to a single Unique ID anyway).

So whenever we're on a non-totals row, the measure will do the standard calculation for the DistinctCountValue. If we're on a totals row, it will use SUMX to get the sum of each DistinctCountValue when your table is at the UniqueID level, i.e., add up 2 + 1 + 1 to get 4 in this case. Divide that by the number of count of unique IDs, and you get to 1.33.

3 REPLIES 3

KGrice

Established Member

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

08-18-2016
12:37 PM

Hi @melz63017. You can get the average to display in the totals row:

To get the Distinct Values measure, I started with a couple of building blocks, shown above as the first two measures.

DistinctCountValue = DISTINCTCOUNT(TableName[Value])

DistinctCountUniqueID = DISTINCTCOUNT(TableName[Unique ID])

The last measure in the table is the Distinct Values measure, which uses both of the building blocks defined above:

Distinct Values = IF( HASONEVALUE(TableName[Unique ID]),

[DistinctCountValue],

SUMX(VALUES(TableName[Unique ID]), [DistinctCountValue]) / [DistinctCountUniqueID]

)

The HASONEVALUE function at the beginning of the IF statement checks if your current evaluation context has more than one distinct Unique ID value. So on the table rows that show one Unique ID each, there's one value. For a totals row, there would be multiple values (as long as you don't have your table filtered to a single Unique ID anyway).

So whenever we're on a non-totals row, the measure will do the standard calculation for the DistinctCountValue. If we're on a totals row, it will use SUMX to get the sum of each DistinctCountValue when your table is at the UniqueID level, i.e., add up 2 + 1 + 1 to get 4 in this case. Divide that by the number of count of unique IDs, and you get to 1.33.

melz63017

Frequent Visitor

Re: Average based on distinct values in another column

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

08-18-2016
02:01 PM

Hi,

Thanks for the help. Is there a way just to show the measure (i.e. Average)? It doesn't have to show up in place of Total but I was wondering if there was formula to just get the measure.

Thanks!

Melissa

KGrice

Established Member

Re: Average based on distinct values in another column

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

08-18-2016
02:36 PM

Once the measure's created, you can drag it anywhere. You can use the one already created for the table visual, or just use the last part of that formula. If I take this version of the measure:

Distinct Values = SUMX(VALUES(TableName[Unique ID]), [DistinctCountValue]) / [DistinctCountUniqueID]

I can drop that on the report in a visual, like a card, and get this: