Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Find max and min of dynamic range to normalize measure values

I have a table with two years of revenue for vendors that sell multiple products. I'm trying to determine max and min values for both a column and a calculated measure that are dynamically filtered, but I'm running into issues with both. The column measure retrieves the max and min values across products for only the current vendor even when I apply ALL to vendors.

 

maxRevenue = CALCULATE(MAX([Vendor YoY]), ALL('Vendor Performance'[Vendors]), ALL('Vendor Performance'[Product]))

 

 

With the measure, the error I get is "The MAX function only accepts a column reference as the argument number 1."

 

I need to do the following. To be clear, I only need help with step 2:

  1. Calculate year-over-year (YoY) revenue (done)
  2. Normalize YoY revenue by finding the maximum and minimum values of the currently selected vendors and products, calculated as
    RevYoY_normalized = (Rev - Rev_min)/(Rev_max - Rev_min)
  3. Normalize Year2 revenue in the same way as step 2
  4. Calculate a distance metric for the two normalized measures as 
    Distance = sqrt(RevYoY_normalized^2 + Rev_normalized^2)
  5. Rank the selected vendors based on the distance metric

     

    Here's a link to my sample .pbix file.

Any help is very much appreciated!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

There was something about this problem that seemed familiar. Sure enough, when I searched my past posts I found another instance where I needed to dynamically calculate the maximum value of a matrix. The solution for that ultimately was the same as for this. To normalize revenue across vendors, I did the following:

Revenue Normalized =
VAR vendorRev = SUM(Sales[Revenue])
VAR maxRev = MAXX(ALLSELECTED(Sales[Vendor]), vendorRev)
VAR minRev = MINX(ALLSELECTED(Sales[Vendor]), vendorRev)
VAR normalizedRev = DIVIDE(vendorRev - minRev, maxRev - minRev, BLANK())
RETURN
normalizedRev

Rather than using the VAR, I could have used a separate measure built with the same formula.

View solution in original post

1 REPLY 1
Anonymous
Not applicable

There was something about this problem that seemed familiar. Sure enough, when I searched my past posts I found another instance where I needed to dynamically calculate the maximum value of a matrix. The solution for that ultimately was the same as for this. To normalize revenue across vendors, I did the following:

Revenue Normalized =
VAR vendorRev = SUM(Sales[Revenue])
VAR maxRev = MAXX(ALLSELECTED(Sales[Vendor]), vendorRev)
VAR minRev = MINX(ALLSELECTED(Sales[Vendor]), vendorRev)
VAR normalizedRev = DIVIDE(vendorRev - minRev, maxRev - minRev, BLANK())
RETURN
normalizedRev

Rather than using the VAR, I could have used a separate measure built with the same formula.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.