Scaling Numbers within Power BI

[phodges]

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.

Measures:

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]),
IF
(
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

[v-jayw-msft]

Hi @phodges ,

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

Switch(true(),condition1,result1,condition2,result2,etc...)

Best Regards,

Jay

[phodges]

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!

[phodges]

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.