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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
malone_henrique
Regular 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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.