Forum Discussion

cghanta's avatar
cghanta
Helper I
1 year ago
Solved

Percentage from Text Columns

I have 2 questions.  I need to create 2 measures from text columns and display as cards ignoring the slicer.

I have a table UPS, it has a Location colum and Maintenance Bypass column (Text) & Dual UPS column (Text).

 

LocationMaintenance BypassDual UPS
city1Yesn/a
city3Yesn/a
city3YesYes
city4Non/a

 

Measure1 = percentage of locations that do not have Maintenance Bypass, ignoring the location slicer selection

Measure2 = percentage of locations that have Dual UPS, ignoring the location slicer selection.

Thnaks in Advance.

Need Help
  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    Hi cghanta Try these please 

    Measure1 = 
VAR TotalLocations = COUNTROWS(ALL(UPS))
VAR NoMaintenanceBypass = COUNTROWS(
    FILTER(ALL(UPS), UPS[Maintenance Bypass] = "No")
)
RETURN
DIVIDE(NoMaintenanceBypass, TotalLocations, 0) * 100
    Measure2 = 
VAR TotalLocations = COUNTROWS(ALL(UPS))
VAR WithDualUPS = COUNTROWS(
    FILTER(ALL(UPS), UPS[Dual UPS] = "Yes")
)
RETURN
DIVIDE(WithDualUPS, TotalLocations, 0) * 100

5 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Hi cghanta Try these please 

    Measure1 = 
VAR TotalLocations = COUNTROWS(ALL(UPS))
VAR NoMaintenanceBypass = COUNTROWS(
    FILTER(ALL(UPS), UPS[Maintenance Bypass] = "No")
)
RETURN
DIVIDE(NoMaintenanceBypass, TotalLocations, 0) * 100
    Measure2 = 
VAR TotalLocations = COUNTROWS(ALL(UPS))
VAR WithDualUPS = COUNTROWS(
    FILTER(ALL(UPS), UPS[Dual UPS] = "Yes")
)
RETURN
DIVIDE(WithDualUPS, TotalLocations, 0) * 100
    • cghanta's avatar
      cghanta
      Helper I
      1 year ago

      Hi Akash_Varuna

      for the Measure2, the Maintenance Bypass value has changed from No to Yes for city4, and it is displaying (Blank) value, how can I display it as 0.00 instead.


      for the Measure1, how can I exclude a location city1

      thank you in advance

  • cghanta's avatar
    cghanta
    Helper I
    1 year ago

    the following mesaure works, 

    %No Manit Bypass = DIVIDE( CALCULATE( COUNTROWS( UPS ), UPS[Maintenance Bypass] = "Yes" ), COUNTROWS( UPS ) )

    how do I add filter to ignore the slicer?  The above is calculating per row, due to the slicer (giving  answer 1 for teh 1st 3 cities), I want it to calculate % for all rows ignoring the slicer (the answer should be 1/4 = .25) and display 25%