Count Distinct Name based on a Calculated Measure's Value

[Anonymous]

Hi,

As my name suggests, I'm a power bi newbie and I've tried searching the community for answers but it doesn't show me the solutions as expected.

I'm trying to get the distinct count of a measure based on the average stats where sales surpass say $5, 000.

Please see the sample below:

Date	Agent Name	Sales Amount	Team Name
2-Aug-21	Agent A	$           3,333.73	Team A
3-Aug-21	Agent A	$           6,484.11	Team A
4-Aug-21	Agent A	$              321.61	Team A
2-Aug-21	Agent B	$        10,502.31	Team B
3-Aug-21	Agent B	$        13,316.60	Team B
2-Aug-21	Agent C	$              240.94	Team C
3-Aug-21	Agent C	$              341.55	Team C
2-Aug-21	Agent D	$           6,935.82	Team D
3-Aug-21	Agent D	$           7,316.62	Team D
4-Aug-21	Agent D	$              528.74	Team D

I've tried using this DAX formula but it always returns the overall distinct count of the table:

Measure1 = average(Sales Amount)

CALCULATE(

DISTINCTCOUNT( table1[Agent Name] ),

FILTER(table1, Measure1 >= 5000)

)

[Anonymous · Accepted solution]

Hi @Anonymous 

I don't particularly understand your needs. Do you want the count of sales over 5000 or the overall average ? Can you provide the results you need ?

If you want to count the number of Agent Names that have sold more than 5000, you can refer to the following measure.

Measure = CALCULATE(DISTINCTCOUNT('Table'[Agent Name]),FILTER('Table','Table'[Sales Amount.2]>5000))

You should change the type of the [Sales Amount.] from text to Decimal Number .

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@Anonymous 

what's the expected output? do you want distinctcount agents?

maybe you can try

Measure = 
VAR tbl=SUMMARIZE('Table (2)','Table (2)'[Agent Name],"avg",AVERAGE('Table (2)'[Sales Amount]))
return COUNTROWS(FILTER(tbl,[avg]>5000))