How to count drop in trip

[Anonymous]

Could anyone help recommend formula please. For example, I would like to count distinct Store name by trip and also provide ranking as in count drop column. Thank you.

Trip no.	Store name	Count drop
Trip1	A	1
Trip1	B	2
Trip2	A	1
Trip2	B	2
Trip2	C	3

[harshnathani · Accepted solution]

Hi @Anonymous ,

Incase you want a Calculated Column

count1 drop = 
RANKX (
    FILTER (
        RankDrop,
        'RankDrop'[Trip no.]
            = EARLIER ( 'RankDrop'[Trip no.] )
    ),
    'RankDrop'[Store name] ,,ASC
)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[v-eqin-msft]

Hi @Anonymous ,

For my test , maybe you used the formula for "New Column" . It is used for "New measure".

Best regards,

Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

According to my understanding, you want to rank based on two columns , right?

You could use the following formula:

count drop =
RANKX (
    FILTER ( ALL ( RankDrop ), 'RankDrop'[Trip no.] = MAX ( 'RankDrop'[Trip no.] ) ),
    CALCULATE ( MAX ( ( 'RankDrop'[Store name] ) ) ),
    ,
    ASC
)

My visualization looks like this:

Is the result what you want? If not, please upload some detailed data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[Anonymous]

I have try on your formula but there is alert shown "A circular dependency was detect"

[amitchandak]

@Anonymous ,For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

You can try like this too

Count Drop = countx(filter(Table,[Trip no]=[Trip no] && [Store name]<=[Store name]),[Store name])

[pranit828]

Hi @Anonymous 

Calculate(DISTINCTCOUNT(table[store_name]), allexcept(table, table[trip_id))

For rank you can refer to the video 

https://www.youtube.com/watch?v=3u0WJFKxGJk

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Regards,
Pranit