Forum Discussion

charles_o's avatar
charles_o
Helper I
8 years ago
Solved

Scenario Help

Hi- I have data that is organized by project in rows with a column of expectations of likelyhood.

 

Certain

Expected

Likely

Hopeful

 

I need to then be able to sort this data into 3 scenarios- Low Base High. The scenario labels do not exist in my main table and I need to create that link.

 

Low - Certain

Base - Certain, Expected

High - Certain, Expected, Likely, Hopeful

 

How can I do this? I tried creating a new table( with Low-Certain; Base- Certain, Expected....) and merging it with my main table but it did not work the way I wanted it to

 

 

 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi charles_o,

     

    You can try the approach of McCow. Another method.

    1. Create a middle table for all the scenario.

    Scenario

    Low
    Base
    High
    Low,Base
    Low,High
    Base,High
    Low,Base,High

    2. Create a table for slicer.

    SlicerScenario

    LowLow
    BaseBase
    HighHigh
    LowLow,Base
    BaseLow,Base
    LowLow,High
    HighLow,High
    BaseBase,High
    HighBase,High
    LowLow,Base,High
    BaseLow,Base,High
    High

    Low,Base,High

    3. Create relationships and change the filter direction to Both.

    Scenario_Help2

     

    Best Regards,

    Dale

13 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi charles_o,

     

    Is this your solution?

    Scenario =
VAR allLikelyhood =
    CALCULATE (
        CONCATENATEX ( VALUES ( Table1[Likelyhood] ), [Likelyhood], "," ),
        ALLEXCEPT ( Table1, Table1[Project] )
    )
RETURN
    IF (
        allLikelyhood = "Certain",
        "Low",
        IF (
            allLikelyhood = "Certain,Expected",
            "Base",
            IF ( allLikelyhood = "Certain,Expected,Likely,Hopeful", "High", "ERROR" )
        )
    )

    Scenario_Help

     

    Best Regards,

    Dale

    • charles_o's avatar
      charles_o
      Helper I
      8 years ago

       

       

      EDIT: Got to what I wanted with this- One last ask below!

       

       

      Scenario =

      IF (
      Table1[Likelyhood] = "Certain",
      "Low,Base,High",
      IF (
      Table1[Likelyhood] = "Expected",
      "Base,High",
      IF ( Table1[Likelyhood] = "Likely", "High",IF (Table1[Likelyhood] = "Hopeful", "High")
      )
      )
      )

      Looks like this:

       

       

       

      I want to use a slicer on my dashboard to display the data by "Low",  "Base", or "High". Currently my options are "Low,base,High", "Base,High", "high".

       

      Any Suggestions?