Forum Discussion

AlanRGroskreutz's avatar
3 years ago
Solved

Trouble using SELECTEDVALUE in already filtered values.

This might be a bit long, so I appologise in advance.

I'm trying to set up a What-if parameter scenario that will simulate changes in market share.  This implies that an increase in one item of a group will have a proportional decrease in the rest of the group.  The example is airline routes.  Here is the table relationship structure.

The link for both the outermost tables with the airports table is the departure airport code (ICAO).  The market to be analized is determined by filtering on "City" from the airport table, and "ades" (destination) from the fact table on the left.  This will give a list of all the airports from that city that have flights to that destination.

Now, using a slicer based upon the "Departure Airports" table, I want to be able to select which departure airport will be the driver of the change in market share, and have that change affect the other routes in the filtered group.

Here is an example in Excel for a London market where the flights from Heathrow increase 10%.  The decreases in the other departure airports is proportional to their percentage of the flights from that smaller group that does not include the selected departure airport.  This way the total flights from the market stays the same.

When I try to set it up in PowerBI, I have trouble capturing the number of flights from the selected Departure Airport as a variable.   Here are the slicers and table organized in the same manner as the relationship image to be able to show which tables the slicers are working on.

And here is the code to try to calculate "Sum of other origin flights" (right side column in the table).

Total of other orign flights =
VAR SelectedOriginFlights =
    CALCULATE (
        FIRSTNONBLANK ( 'pbi GESTAR_adep_ades_distance'[flights], 1 ),
        FILTER (
            'pbi GESTAR_adep_ades_distance',
            'pbi GESTAR_adep_ades_distance'[adep]
                = SELECTEDVALUE ( 'Departure Airports Selector'[DepartureAirport] )
        )
    )
VAR AllFlights =
    CALCULATE (
        SUM ( 'pbi GESTAR_adep_ades_distance'[flights] ),
        ALLSELECTED ( 'pbi GESTAR_adep_ades_distance'[adep] )
    )
VAR OtherOriginFlights = AllFlights - SelectedOriginFlights
RETURN
    OtherOriginFlights

 

Is this the right approachand I'm just missing something?  Is it even possible?

  • Wow, I would have never though to define variables within an ADDCOLUMN call, thanks.

    There is, however one small problem.  When I move everything over to my actual data, the calculated table Departure Slicer has over 1000 rows.  This is a bit much for the end user to scroll through a slicer to find the airport they want to select. 

    What I’ve been trying to do since you replied was creating a stand-alone table DepartureSlicer2 in Power Query and then use the columns Continent, Country, and City from the Airport table as slicers to filter the 'Departure Slicer' [AirportCode] slicer down to just the ones that are in the interested area.  I have the table relations set this way so that the selection from DepartureSlicer2 does not influence Flight details.

     

    That way the list of departure airports in the data table and the departure airports in the slicer where you select the sa variable are the same.  As you can guess, this doesn't work.  I’ve added the .pbix file so you can see for yourself.  Any help or guidance would be great. Sorry I wasn’t more explicit in the previous reply.

    What-if Test Project 00.02.pbix 

12 Replies

  • The usual safe approach is to use variables to protect from context  transistions.

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Please show the expected outcome based on the sample data you provided.

    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • AlanRGroskreutz's avatar
      AlanRGroskreutz
      Icon for Helper II rankHelper II

      Sorry about that.  I hope this will provide all the necessary info.
      Here are sample tables for the three that are shown in the relationship image.
      For the RouteFacts table

      adepadesFlights
      EGAALEBL126
      EGBBLEBL637
      EGCCLEBL1213
      EGGDLEBL676
      EGGPLEBL394
      EGGWLEBL870
      EGKKLEBL3289
      EGLCLEBL140
      EGLFLEBL103
      EGLLLEBL2017
      EGNXLEBL460
      EGPFLEBL118
      EGPHLEBL537
      EGSSLEBL1374

       

      For the DeaprtureArea locator table

      AirportCodeContinentCountryCity
      EGAAEuropeUnited KingdomBelfast
      EGBBEuropeUnited KingdomBirmingham
      EGCCEuropeUnited KingdomManchester
      EGGDEuropeUnited KingdomBristol
      EGGPEuropeUnited KingdomLiverpool
      EGGWEuropeUnited KingdomLondon
      EGKKEuropeUnited KingdomLondon
      EGLCEuropeUnited KingdomLondon
      EGLFEuropeUnited KingdomFarnborough
      EGLLEuropeUnited KingdomLondon
      EGNXEuropeUnited KingdomEast Midlands
      EGPFEuropeUnited KingdomGlasgow
      EGPHEuropeUnited KingdomEdinburgh
      EGSSEuropeUnited KingdomLondon



      For the list to select which Departure Airport (Departure Airport Selector) will be the focal point of the what-if change of flights.

      DepartureAirport
      EGAA
      EGBB
      EGCC
      EGGD
      EGGP
      EGGW
      EGKK
      EGLC
      EGLF
      EGLL
      EGNX
      EGPF
      EGPH
      EGSS

       

      There is also a Parameter field, that can range from -1 to 1 in steps of 0.01 to simulate the percentage change of the flights coming from the selected departure airport.

      Filtering for London as the City of departure, EGGW as the airport the parameter affects directly, and 0,05 as the parameter, I was hoping to get something that looks like this.

      adepadesFlightsnew flights% change
      EGGWLEBL8709145,0%
      EGKKLEBL32893268-0,6%
      EGLCLEBL140139-0,6%
      EGLLLEBL20172004-0,6%
      EGSSLEBL13741365-0,6%

      The 5% increase in flights from EGGW is 43,5 (44) flights, and those flights would come from the other 4 airports in proportion to the % they make up of the smaller group of airports (without the airport that had the increase).

      I need to be able to multiply the flights for the selected departure airport by the parameter (1+0.05), and subtract frm the other departure airports flights an amount proportional to their portion of the remaining flights.  I just can't seem to be able to construct the table. Not with SUMMARIZE or anything.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User

        That was a nice challenge, thank you!

         

        One small issue:  It is your responsibility to include the slicered airport in the table selection.

         

        And as you can imagine the totals may be off a little due to rounding issues.