How to find MAX of RANK

[drew61199]

I have a fairly complicated report in Excel that I'm migrating to Power BI. Here is the calculation breakdown (and if anyone has an easier way to calculate....I'm open to it):

• Contains over 10,000 locations and 20 metrics
• Each metric is ranked individually - due to the "uniqueness" of some though, I may have a range of 1-200 for a metric (meaning a lot of zeros in the data) or 1-9000 for another. A small vs large range does not necessarily relate to "importance" though - the ones with a lot of zeros are often VERY important b/c we expect zero in the data, so any other value is bad.....whereas one with lots of RANK values is just a common metric with a lot of decimal places
• I also have a weighting factor for each KPI, so originally took RANK * WEIGHT and totalled those for a COMPOSITE SUM (highest score = top opportunity). The issue is a location may do really well in all metrics except one of the large "1-9000 ranked metrics" and completely ruin its score. To compensate for this in Excel and "equalize" all metrics, I put them on a 100 point scale and created a "factor" with =100/MAX( RANK column ) for each individual RANK column. I then changed my calcualtion to RANK * WEIGHT * FACTOR and totalled for COMPOSITE SUM. An added benefit is the COMPOSITE SUM also falls into a 1-100 scale which is often just simpler on an end user to comprehend....1 is great...90s is a fire alarm situation. 

In case I didn't explain that well, below is an example of my Excel pivot table. Data values are one column and rank values are immediately following it. I have the weight and factors at far right.

MY ISSUE FOR POWER BI:

I have all the data, facilities, metrics, ranks, but cannot figure out how to find the MAX of a RANK.

For example, I have rank measures created for each metric:

rank_test_data_1 = IF(NOT(ISBLANK([_test_data_1])),RANKX(ALL(Report[Location]),[_test_data_1],,ASC,DENSE))

If I create a measure for max_test_data1 = MAX(rank_test_data_1) I receive an error ("column cannot be found or may not be used in this expression"). I'm not tied to my current Excel calculation, but I absolutely need a way to equalize the various metrics as the "WEIGHT FACTOR" is how we prioritize items of focus (which can change throughout the year depending on market demands, leadership expectations, etc). 

[amitchandak]

@drew61199 , If I want to know max rank, I need a measure

maxx(values(Report[Location]), [rank_test_data_1])

[drew61199]

Thank you for the reply. The formula works great in a card, but when added to the matrix, it displays the rank for each individual location. Ultimately I need DIVIDE(100, FACTOR), so I would expect 100 / 479 = .209, but as you can see, the factor is unique for each location

[v-chenwuz-msft]

Hi @drew61199 ,

Cay you try

FACTOR_test1 = CALCULATE( MAXX(values(Report[Location]), [rank_test_data_1]), ALL(Report))

If not work, please share your some sample data and expect result.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.