How To Create a Table with Ranges based on starting value

[Anonymous]

I want to make a visual table that will show Min/Max ranges in a table for different tiers.

So I am needing to start with a value...  Let's say it's 7500 (and this value will change and will not be constant)..

7500 will start the 5th tier... To find the range , I will add 7500 to the number to find the max, and subtract 7500 to find the minimum for that tier..  

Subsequent tiers below tier 5 will have 15000 subtracted from the Minimum value of tier 5, to "fill in the blank" for tier 4, another -15000 to find the min value of tier 3, etc...

Tiers above tier 5 will have +15000 added to each minimum..

Is there a way to create a table that will be able to visualize the min/max of each tier dynamically as the main value will change (the original 7500)?

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download PBIX file with the following code.

You can do this in Power Query with this code

let 
    n = 7500,
    n2 = n*2
in 
#table
(
    type table 
    [
        #"Minimum" = number,
        #"Maximum" = number,
        #"Tier" = number
    ],
    {
        {-9999999, n2*-3, 1},
        {(n2*-3)-0.01, n2*-2, 2},
        {(n2*-2)-0.01, n2*-1, 3},
        {(n2*-1)-0.01, 0, 4},
        {0.01, n2, 5},
        {n2+0.01, n2*2, 6},
        {(n2*2)+0.01, n2*3, 7},
        {(n2*3)+0.01, n2*4, 8},
        {(n2*4)+0.01, 9999999, 9}
    }
)

Which creates this

I'm not sure how you intend to get the value 7500 (or whatever it is) into the code but for now I've typed it in.  This value can be loaded via other means.

You could do this in DAX using the Table Constructor

Table constructor - DAX | Microsoft Docs

but you can't name the columns using that approach.  I'd go with the Power Query solution as it's more flexible.

Regards

Phil