Help Creating a Simulator for User-Inputted Series of Ranges and Columns Based on the Upper Bound

[threedre34]

Hi, everyone! I need to create the table below (columns E-J in the image), which is dynamic based on user inputs for the Max Number and Range. Columns E and F will be user inputs (e.g., 864 as the Max Number and 54 as the Range).

Based on these inputs, the values for Columns G to J will depend on Column F:

• Column G (CC1): This will count the number of entries in Column B (Data 2) that are less than or equal to Column F. For example, Cell G2 will count the entries in Column B that are less than or equal to 54, while Cell G3 will count those that are less than or equal to 108 but greater than 54.

• Column H (CC2): This will be calculated by dividing Column G by the total count of Column B (Data 2).

• Column I (CC3): This will be the sum of entries in Column A (Data 1) that are less than or equal to Column F, divided by the total number of entries in Column A.

Note: Data 1 and Data 2 are measures that I created.

I have been struggling with this task for the past few days, especially regarding the dynamic columns based on user inputs. I'm also quite new to Power BI, so any help would be appreciated. Thank you!

[threedre34]

It works! Thank you @rohit1991!

[rohit1991 · Accepted solution]

Hi @threedre34 

Could you please follow the steps below :

1. Load your table Data with columns Data1, Data2.

2. Create two What-If parameters

Max Number (measure: [Max Number Value])

Range (measure: [Range Value])

3. Create helper table:

BinsIndex = GENERATESERIES(1, 1000, 1)   -- increase if needed

4. Create measures:

Bin Upper =
VAR k = SELECTEDVALUE(BinsIndex[Value])
RETURN k * [Range Value]

CC1 =
VAR r = [Range Value]
VAR k = SELECTEDVALUE(BinsIndex[Value])
VAR upper = k * r
VAR lower = (k - 1) * r
RETURN COUNTROWS( FILTER(Data, Data[Data2] > lower && Data[Data2] <= upper) )

CC2 = DIVIDE([CC1], COUNTROWS(Data))

CC3 =
VAR r = [Range Value]
VAR k = SELECTEDVALUE(BinsIndex[Value])
VAR upper = k * r
RETURN DIVIDE(
   COUNTROWS( FILTER(Data, Data[Data1] <= upper) ),
   COUNTROWS(Data)
)

Show Bin = IF( [Bin Upper] <= [Max Number Value], 1 )

5. Build a Matrix/Table:

Rows: BinsIndex[Value]

Values: Bin Upper, CC1, CC2, CC3 (format CC2/CC3 as %)

Filters (visual): Show Bin >> is not blank (hides bins above Max Number)

Add slicers for Max Number and Range.