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

[Keith011]

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

Exhibit 2 : This is what I really need

[Keith011]

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

Exhibit 2 : This is what I really need

[Anonymous]

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

[Keith011]

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

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

[nickvanmaele]

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 · Accepted solution]

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] )
)