How to COUNT the number of same Rank occurrences in a column generated by RANKX?

[test2sa]

The idea is I have numerous quotes and numerous suppliers that can submit cost bids to that particular quote. I want to see how many times each supplier achieved a particular rank from all the quotes.

I already have a RANKX formula that ranks my original table.

Rank within a quote = RANKX( ALL( my_excel_sheet_table[Supplier]), [cost calculated measure],,ASC,DENSE)

Cost is a complicated, calculated DAX measure, not a real column. Quote Number and Supplier are real columns from the excel spreadsheet.

And then, unfortunately, because I don't know how make it work in DAX, I literally export this table as an excel sheet so I can reimport it into PowerBI to be able to use the "Rank within a quote" as a true column, since you can't ever stick measures into functions like COUNT, only real columns.

Rank Count = COUNT(my_new_exported_excel_table[Rank within a quote])
And now I can see how many times a particular rank occured for a particular supplier across all of the quotes.
But this is a terrible way of doing it! We have many different types of cost columns we want to do this for. I cannot simply export the data for every single one! I've tried doing RANKX at the power query level and it doesn't make things better because I can't use the Cost measure, since Cost isn't a real column, it's a calculated DAX measure.

I'm not sure how to deal with these issues. Please help?

[johnt75]

You can create tables as variables within DAX measures and then perform aggregations on them, e.g.

Num #2 rank =
VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[quote], 'Table'[supplier] ),
        "@rank", [Ranking measure]
    )
VAR Result =
    COUNTROWS ( FILTER ( SummaryTable, [@rank] = 2 ) )
RETURN
    Result

[test2sa]

This piece you gave me worked, but what's the best way to deal with the fact there is more than one rank?
Should I create a seperate measure for each rank?

Num #2 rank
Num #3 rank
Num #4 rank
Num #5 rank

And then somehow put them back together into a single column "Rank Count"?

Utimately, I am trying to create:

[johnt75]

Does the calculation need to be dynamic, e.g. respond to slicers etc? If not, then you could create a calculated table using the code from the SummaryTable variable. You would then be able to write measures etc based on that, but importantly you would be able to group by the values from the ranking column. So a simple COUNTROWS across the new calculated table would be enough.

[test2sa · Accepted solution]

So I didn't fully understand what you were saying, but then I think I accidentally ended up doing exactly what you were saying.

I created a new table within Power BI using the Modeling -> "New table" button

Rank test table = ADDCOLUMNS(
SUMMARIZE('Table', 'Table'[quote], 'Table'[supplier]),
"Rank", [Ranking measure]
)

And I created a new measure within that new table:

Rank Count = COUNT('Rank test table'[Rank])

Not the greatest solution but allows me to get a table within PowerBI using the [Ranking measure] formula instead of exporting it as excel file and then reimporting it as a new table.