Forum Discussion

karthikmiriyala's avatar
karthikmiriyala
Frequent Visitor
3 years ago
Solved

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

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(AveragePetal Len(AVG)Petal Width(AVG)

Q 1

    

Q 2

    

Q 3

    
Q 4    

 

Thanks in advance,

Karthik.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

3 Replies

    • karthikmiriyala's avatar
      karthikmiriyala
      Frequent Visitor
      3 years ago

      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 LengthSepal WidthPetal LenthPetal 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's avatar
        Anonymous
        Not applicable
        3 years ago

        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