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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Frequent Visitor

Scaling Numbers within Power BI

Hello Power BI Community,


I am working on a project that requires scaling of data within Power BI. Attached is an example file with a small dataset. The dataset I am using is much larger and more complex, but this should illustrate the idea. I tried to attach the actual file but I guess you can't directly attach the files here so I have added some screen shots and the code for my measures below.


I have determined a way to scale numbers in a table dividing the original dataset by a number typed in by the user. The user types the index of the row they want to scale in a slicer set to "greater than or equal to" and then in another similar slicer they type the scaling value for that index row. The calculation is then performed to scale the number. This works effectively if the number of rows needed to be scaled is small. But for each row the user wishes to scale, a new set of tables for the slicers would need to be created so all the math can be done in the same table. I need all this in the same table because I need the total of the scaled numbers.


As you can see, doing it in this way would mean needing many single column tables to hold the options for the number choices used by the user. It would also mean many IF statements within the scaling measure. Does anyone know a better way of doing this?


A few notes on this:

- I know a "WhatIf Parameter" can be used to define a number choice, but due to the limitations of the "WhatIf Parameter" (it can only hold 1000 numbers) this will not work because I need a higher range of number choices. For the simple example I provided yes they would work, but not for my larger dataset.

- I know I can also use GenerateSeries() to produce a calculated table for the number choices, which at least may be better than full, "physical" tables, but I have found a similar problem as with the "WhatIf Parameter" in that it shows strange behavior generating numbers in a larger number range.

- I need to be able to scale the numbers in a row independantly of the other rows. Some many need to be scaled and others may need to stay as they are.


Thank you in advance for any thoughts or comments.




Numbers to be scaled measure = AVERAGE('Numbers to be scaled'[Numbers to be scaled]) //used for grabbing the numbers to be scaled for later calculations


Scaled Numbers = IF //used for scaling the numbers
SELECTEDVALUE('Numbers to be scaled'[Index]) = MIN('Index of Number to be Scaled 1'[Type number in]),
[Numbers to be scaled measure]/MIN('Scaling Value 1'[Type number in]),
SELECTEDVALUE('Numbers to be scaled'[Index]) = MIN('Index of Number to be Scaled 2'[Type number in]),
[Numbers to be scaled measure]/MIN('Scaling Value 2'[Type number in]),
[Numbers to be scaled measure]


Totaling of Scaled Numbers = IF //used for calculating the proper total of the scaled numbers in the "Total" row of the table
HASONEFILTER('Numbers to be scaled'[Index]),
[Scaled Numbers],
SUMX('Numbers to be scaled', [Scaled Numbers])


Difference of numbers scaled from actual numbers = AVERAGE('Numbers to be scaled'[Numbers to be scaled]) - [Scaled Numbers] //used for conditional formatting to highlight which rows were scaled

Community Support
Community Support

Hi @phodges ,


You could update the pbix to onedrive and share the link here.For switch() function you could try below formula.




Best Regards,


Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Thank you for your reply. Yes I have tried the switch function, but because I have 20 possible conditions it takes a very long time. It seems, by all that I have been reading, this is the proper way to do, but it is far too slow. I will keep working on it and if I find a solution I will post in case someone else needs a similar solution. If you, or anyone, has any other ideas I would love to hear them. Thanks again for your help!

Frequent Visitor

So as an update to my earlier post,


- I figured out why the GenerateSeries() wasn't working properly. All I needed to do was set it to a fixed decimal value and it worked fine even with the higher number range.

- I was using a lot of IF statements to create the options I needed, but I found that Switch() can be used to clean that up a lot.

- I am still using a lot of tables (now they are all GenerateSeries() instead of hard coded tables) for the number selections by the users so if anyone knows of a better way of doing that I would appreciate the help.

Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.