Count Occurrences of #1 RankX Measure

[amtbew]

Hi,

My data is as follows.

I have two Measures: 

Lowest Proposal = min(Proposal[Vendor Bid])

&

Proposal Rank = if(ISBLANK([Lowest Proposal])
, blank ()
, rankx(
filter(ALL(Proposal[Vendor ID]), not (ISBLANK([Lowest Proposal])))
, [Lowest Proposal]
,
,1
)
)

What I'm trying to do is get a count of the number of times a Vendor had the #1 Proposal Rank accross all of the Product IDs

So the expected outcome with this data would be 

A1 - 2

A2 - 1

Thanks

[Dangar332 · Accepted solution]

hi, @amtbew  

try below measure

rank = 
var a = SUMMARIZE(
          'Table',
          'Table'[product id],
          'Table'[lowest proposal],
          'Table'[vender id],
          "rank",RANK(
                    DENSE,
                    ALL('Table'),
                    ORDERBY('Table'[lowest proposal],ASC),,
                    PARTITIONBY('Table'[product id])
                )
        )
return
COUNTX( FILTER(a, [rank]=1),'Table'[vender id])

[Ahmedx]

pls try this

[Ahmedx]

or this try

[amtbew]

Both ones you posted seemed to work. Thanks

[Dangar332 · Accepted solution]

hi, @amtbew  

try below measure

rank = 
var a = SUMMARIZE(
          'Table',
          'Table'[product id],
          'Table'[lowest proposal],
          'Table'[vender id],
          "rank",RANK(
                    DENSE,
                    ALL('Table'),
                    ORDERBY('Table'[lowest proposal],ASC),,
                    PARTITIONBY('Table'[product id])
                )
        )
return
COUNTX( FILTER(a, [rank]=1),'Table'[vender id])

[amtbew]

Thanks @Dangar332  howver it's returning the following error

The column 'lowest proposal' specified in the 'SUMMARIZE' function was not found in the input table.

Perhaps because that is a measure not a column?

Here's a pbix

https://www.dropbox.com/scl/fi/q84z8jf3hvn34cuyyu1n8/CountRankX.pbix?rlkey=l9ugoaa87n2r3qbbjdcrke9ll...

Thanks

[Dangar332 · Accepted solution]

hi, @amtbew 

try below

Measure 3 = 
var a = ADDCOLUMNS( SUMMARIZE(Proposal,Proposal[Product ID],Proposal[Vendor ID]),"low",[Lowest Proposal],"rank",[Proposal Rank])
return
 COUNTX(FILTER(a,[Proposal Rank]=1),Proposal[Vendor ID])

[amtbew]

This one worked. Thank you