Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
malone_henrique
Frequent Visitor

Distinct Count ignoring one column plus calculate average

Hello, all!

 

I have the following table 

malone_henrique_0-1713968479352.png

And im trying to get a simple DISTINCT COUNT[ID] but its not working because the column Type has two different values (B and E).

If i use a mesure, it works BUT im trying to use a Column because after i get the distinct count, ill need get the average.

Like the example below:

malone_henrique_1-1713968695737.png

In short, i need to get the average of distinct count[id].

 

Ive tried create a column with 

CALCULATE(DISTINCTCOUNT(Consult[id]) / DISTINCTCOUNT(Consult[date].[Month]), ALLSELECTED(Consult[date].[Day]))
 
But its not working because its counting the same ID twice when the ID has the B and E type.
 
If there is a way to get the column average that i need using a measure, ill be glad to learn how.
 
Thanks! 
1 ACCEPTED SOLUTION
barritown
Super User
Super User

Hi @malone_henrique,

Try this piece of code:

barritown_0-1713989272528.png

In plain text:

Avg_Distinct_ID = 
VAR _tbl = SUMMARIZE ( 'Table', [Year], [Month], "dValue", DISTINCTCOUNT ( 'Table'[ID] ) ) 
RETURN AVERAGEX ( _tbl, [dValue] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

View solution in original post

1 REPLY 1
barritown
Super User
Super User

Hi @malone_henrique,

Try this piece of code:

barritown_0-1713989272528.png

In plain text:

Avg_Distinct_ID = 
VAR _tbl = SUMMARIZE ( 'Table', [Year], [Month], "dValue", DISTINCTCOUNT ( 'Table'[ID] ) ) 
RETURN AVERAGEX ( _tbl, [dValue] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.