Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count Distinct Name based on a Calculated Measure's Value

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:

 

DateAgent NameSales AmountTeam Name
2-Aug-21Agent A $           3,333.73Team A
3-Aug-21Agent A $           6,484.11Team A
4-Aug-21Agent A $              321.61Team A
2-Aug-21Agent B $        10,502.31Team B
3-Aug-21Agent B $        13,316.60Team B
2-Aug-21Agent C $              240.94Team C
3-Aug-21Agent C $              341.55Team C
2-Aug-21Agent D $           6,935.82Team D
3-Aug-21Agent D $           7,316.62Team D
4-Aug-21Agent D $              528.74Team 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)
)
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.