Creating a table with values changing respective with what if parameter.

[karthikmiriyala]

I am creating a Dashboard with two what-if parameters. Able to create a Scatter plot with Quadrants, changing with respective what-if parameters As shown in the picture. 

The Quadrants will change accordingly with what-if parameters.

 

This is the code used for Color respectively.

Colors =

VAR X = SELECTEDVALUE(iris[sepal.length])

VAR Y = SELECTEDVALUE(iris[sepal.width])

VAR selected_X = 'Sepal Length'[Sepal Length Value]

VAR selected_Y = 'Sepal Width'[Sepal Width Value]

RETURN

SWITCH(

TRUE(),

X <= selected_X && Y <= selected_Y , "#48B847",

X <= selected_X && Y > selected_Y , "#FF5733",

X > selected_X && Y <= selected_Y , "#805B86",

X > selected_X && Y > selected_Y , "#000000",

"#33FF96"

)

I need to create a table, that changes the values with what parameters.

Sepal Lenght(Average)	Sepal Width(Average	Petal Len(AVG)	Petal Width(AVG)
Q 1
Q 2
Q 3
Q 4

Thanks in advance,

Karthik.

[Anonymous]

Hi @karthikmiriyala ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[karthikmiriyala]

Hi @Anonymous, 
Thank you very much for the reply, Please find the link for the dataset:  https://gist.githubusercontent.com/curran/a08a1080b88344b0c8a7/raw/0e7a9b0a5d22642a06d3d5b9bcbad9890c8ee534/iris.csvhttps://gist.github.com/curran/a08a1080b88344b0c8a7

these are the fields 

sepal_length,sepal_width,petal_length,petal_width,species

in the dataset.

I am trying to create a dashboard that will take two inputs from the user, based on those parameters creating a scatter quadrant plot and changing quadrants with respective user input parameters.

1. Created two what-if parameters.

 Sepal Length and Sepal Width.

 2. Created a scatter plot with  X-axis - Sepal Length and Y-Axis - Sepal Width.

Added Constant line X- axis with what-if parameter value Sepal Length'[Sepal Length Value] and Constant line Y-axis with what-if parameter value 'Sepal Width'[Sepal Width Value].

3. So when the user changes the values in the slicer(what if parameter) in the scatter plot two Constant lines changes accordingly.

4. So as a result based on user inputs we will get 4 dynamic quadrants. 

5. For coloring each quadrant points  I used the following code which woking fine. 

Colors =

VAR X = SELECTEDVALUE(iris[sepal.length])

VAR Y = SELECTEDVALUE(iris[sepal.width])

VAR selected_X = 'Sepal Length'[Sepal Length Value]

VAR selected_Y = 'Sepal Width'[Sepal Width Value]

RETURN

SWITCH(

TRUE(),

X <= selected_X && Y <= selected_Y , "#48B847",

X <= selected_X && Y > selected_Y , "#FF5733",

X > selected_X && Y <= selected_Y , "#805B86",

X > selected_X && Y > selected_Y , "#000000",

"#33FF96"

)

I am able to create a scatter plot successfully.

 

Problem Statement :

I am Expecting Something like this, tabular information on the Quderent level.

Slicer: Mean/Median

	Sepal Length	Sepal Width	Petal Lenth	Petal Width
Quadrant 1
Quadrant 2
Quadrant 3
Quadrant 4

If the user select Mean in Slicer the table should show the Mean of each column Quadrant wise, based on the User Sepal Lenght and Sepal Width inputs. 

Same for the Median too.

Note: table value should change according to the two what-if parameters.

Thank you in advance,

Karthik 

[Anonymous · Accepted solution]

Hi @karthikmiriyala,

You can create a new table with two modes to use in formula change calculation mode:

SwitchMode = {"Mean","Median"}

Here are the dynamic measure formulas based on two what-if parameter tables, Quadrant group and mode slicer.

petalLength = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currpLength =
    MAX ( iris[petal_length] )
VAR currpWidth =
    MAX ( iris[petal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currpLength <= [spLength], currpLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currpWidth <= [spWidth], currpWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[petal_length] ),
            IF ( selected = "Median", MEDIAN ( iris[petal_length] ) )
        ),
        FILTER ( ALLSELECTED ( iris ), c1 && c2 )
    )+0

petalWidth = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currpLength =
    MAX ( iris[petal_length] )
VAR currpWidth =
    MAX ( iris[petal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currpLength <= [spLength], currpLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currpWidth <= [spWidth], currpWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[petal_width] ),
            IF ( selected = "Median", MEDIAN ( iris[petal_width] ) )
        ),
        FILTER ( ALLSELECTED ( iris ), c1 && c2 )
    )+0

sepalLength = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currspLength =
    MAX ( iris[sepal_length] )
VAR currspWidth =
    MAX ( iris[sepal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currspLength <= [spLength], currspLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currspWidth <= [spWidth], currspWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[sepal_length] ),
            IF ( selected = "Median", MEDIAN ( iris[sepal_length] ) )
        ),
        FILTER ( ALLSELECTED ( iris ),  c1 && c2  )
    )+0

sepalWidth = 
VAR selected =
    SELECTEDVALUE ( SwitchMode[Value] )
VAR currQuadrant =
    MAX ( 'Table'[Quadrant] )
VAR currspLength =
    MAX ( iris[sepal_length] )
VAR currspWidth =
    MAX ( iris[sepal_width] )
VAR c1 =
    IF ( currQuadrant <= 2, currspLength <= [spLength], currspLength > [spLength] )
VAR c2 =
    IF ( currQuadrant IN { 1, 3 }, currspWidth <= [spWidth], currspWidth > [spWidth] )
RETURN
    CALCULATE (
        IF (
            selected = "Mean",
            AVERAGE ( iris[sepal_width] ),
            IF ( selected = "Median", MEDIAN ( iris[sepal_width] ) )
        ),
        FILTER ( ALLSELECTED ( iris ),  c1 && c2  )
    )+0

Regards,

Xiaoxin Sheng