Forum Discussion

SamTrexler's avatar
SamTrexler
Icon for Helper IV rankHelper IV
9 years ago

Slicer based on measure for two visuals

I have two visuals showing different perspectives of a measure called Fail Percent. The first visual is a table showing the Fail Percent for each location, and the second visual is a map showing the ...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi SamTrexler,

You can create a calculated column, then select it as slicer. For example, if you want to filter to rows where the percentage is less than 7% or not. Please create a calculated column using the formula.

 > 7%=IF(Table[Percentage]>7%,"Yes","No")


Create a slicer including [> 7%], when you select "Yes", it returns all the rows bigger than 7%, otherwise, the result is converse.

Best Regards,
Angelia

  • SamTrexler's avatar
    SamTrexler
    Icon for Helper IV rankHelper IV
    9 years ago

    Hi v-huizhn-msft,

     

    Thanks for the suggestion, I'll see if I can make it work. I generally don't create calculated percentages in a table because they don't summarize correctly. I create percentages as a measure, where the sum of the numerator column and the sum of the denominator column are cacluated based on the relevant filters and then used to calculate the percentage for those filters (e.g., summary level). I'll see if I can make it work in this case.

     

    Regards,

     

    Sam

     

     

    • SamTrexler's avatar
      SamTrexler
      Icon for Helper IV rankHelper IV
      9 years ago

      v-huizhn-msft, I'm unable to avoid a circular reference if I create the percentage as a calculated column. Is there a trick to do this?

       

      Consider a very simplified version of my SQL Server table:

       

      RegionID         varchar(10)

      CountyName  varchar(35)

      StationID         varchar(10)

      Status              varchar(5)

      EventCount        number

       

      A StationID beongs to a RegionID and a CountyName, and for each StationID there is a row with the value of EventCount for Status="Up" and another row for Status="Down". I need to calculate something like

       

      CalcPct = CALCULATE(SUM(EventCount),Status="Down")  / CALCULATE(SUM(EventCount))

       

      Then I can get the percentage for each StationID, as well as the percentage for each RegionID, each CountyName and the overall percentage for all stations. The percentage by Region is displayed in a tabular visual (ideally, a New Matrix that lets me drill from RegionID to StationID), and the percentage by CountyName is displayed on a Filled Map.

       

      If I create this as a measure, it works fine. But I can't create a slicer on a measure, so that I can let the user see the regions, stations and counties for whatever range of percentages they want.

       

      If I create this calculation as a calculated column, I can't avoid a circular reference and so I can't get the calculation I need.

       

      Is there a way to make this work?

       

      Thanks again for your help.

       

      Sam