Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Distintcount with slicer does not work

Dear all, good morning. 

 

I've been having a hard time calculating our sales Hit ratio. The reason is , after I apply a slicer and filter by status, the total won goes wrong.

 

Here is the data

 

 

total_quotes = calculate(distinctcount(table'QTY')

total_won - calculate(distinctcount(table'QTY'),Status="Won")

 

Until here it works well, but if I create a filter with the status and select loss the below happens

 

So I am selecting all QTY that are lost, but it still gives me the total of won as 2...

 

Why is this happening?

Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    In your measure:

    calculate(distinctcount(table'QTY'),Status="Won")

    CALCULATE overwrites the existing filter on Status. So although you filter to Lost the DAX replaces it with Won.

     

    Try

    calculate(distinctcount(table'QTY'),KEEPFILTERS( Status="Won" ))

1 Reply

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    In your measure:

    calculate(distinctcount(table'QTY'),Status="Won")

    CALCULATE overwrites the existing filter on Status. So although you filter to Lost the DAX replaces it with Won.

     

    Try

    calculate(distinctcount(table'QTY'),KEEPFILTERS( Status="Won" ))