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).
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
