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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Keith011
Helper III
Helper III

How do i get the total distinct count of value and apply across the report?

Hi there,

 

The issue is at "distinctCount of Respondent ID". I have about 13,000 rows of data but there's only 205 unique respondent ID. In Exhibit 1, i used a simple "DISTINCTCOUNT([RespondentID])" and it returned me the "distinctCount of Respondent ID" column. Then I did a simple division measure hence i'm getting all 100% in the last column, which is wrong!

 

However, in Exhibit 2 is what i desire. But I manually hard-coded the "205" in it and i'm able to get what i need.

 

My question is : What is the right formula to get the "205"?

 

 

Exhibit 1 : This is what I'm getting

Keith011_0-1671415667666.png

 

Exhibit 2 : This is what I really need

Keith011_1-1671415743967.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but please try something like below.

It is for creating a measure.

 

distinctcount expected result measure: =
CALCULATE (
    DISTINCTCOUNT ( TableName[RespondentID] ),
    ALL ( TableName[Value] )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Keith011
Helper III
Helper III

Hi there,

 

The issue is at "distinctCount of Respondent ID". I have about 13,000 rows of data but there's only 205 unique respondent ID. In Exhibit 1, i used a simple "DISTINCTCOUNT([RespondentID])" and it returned me the "distinctCount of Respondent ID" column. Then I did a simple division measure hence i'm getting all 100% in the last column, which is wrong!

 

However, in Exhibit 2 is what i desire. But I manually hard-coded the "205" in it and i'm able to get what i need.

 

My question is : What is the right formula to get the "205"?

 

 

Exhibit 1 : This is what I'm getting

Keith011_0-1671415667666.png

 

Exhibit 2 : This is what I really need

Keith011_1-1671415743967.png

 

You can use CALCULATE(DistinctCount(Table[RespondentID), REMOVEFILTERS(Table[RespondentID]))

 

This will give you the 205 in each row, and then use your original measure as the numerator as in:

 

DIVIDE([Original Measure], [The New Measure]).

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

i tried CALCULATE(DistinctCount(Table[RespondentID), REMOVEFILTERS(Table[RespondentID]))

but it still returns me the exact value as in my Exhibit 1

Just to check: are you looking for an answer in DAX? If so, you may want to post in the Power BI forum? 

Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but please try something like below.

It is for creating a measure.

 

distinctcount expected result measure: =
CALCULATE (
    DISTINCTCOUNT ( TableName[RespondentID] ),
    ALL ( TableName[Value] )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors