none
count of values with some criteria RRS feed

  • Question

  • Hi,

    I have a dataset like below table and I need to calculate a KPI which determines % Inactive Terminals( in particular month) 

     

    I have difficulties to get values with below criteria:

     

    %Inactive Terminals (e.g September)= Count of terminals that have less than 20 transactions (back up terminals and those that installed on September excluded)  / Count of total terminals(back up terminals and those that installed on September excluded)

     

    TerminalD InstallDate Group TechnicianID September
    1115256 Sunday, September 1, 2019 Backup 9612030 12
    1258745 Monday, September 2, 2019 Standard 9514789 19
    1145877 Tuesday, September 3, 2019 Standard 9612030 17
    1136985 Wednesday, September 4, 2019 Standard 9612030 108
    1254896 Thursday, September 5, 2019 Standard 9501025 205
    1115359 Friday, August 2, 2019 VIP 9514789 27254
    1258848 Friday, August 2, 2019 Standard 9514789 204
    1145980 Friday, August 2, 2019 VIP 9612030 451
    1137088 Friday, August 2, 2019 Standard 9501028 180
    1254999 Friday, August 2, 2019 Standard 9514789 17
    1115462 Wednesday, August 7, 2019 Standard 9612030 0
    1258951 Monday, July 1, 2019 VIP 9612030 652
    1146083 Tuesday, July 2, 2019 VIP 9514789 14598
    1137191 Wednesday, July 3, 2019 Standard 9514789 102
    1255102 Thursday, July 4, 2019 Standard 9501022 496
    1137195 Friday, July 5, 2019 Standard 9501022 205
    1255106 Saturday, July 6, 2019 Standard 9501022 20
    1115569 Sunday, July 7, 2019 Standard 9501022 402
    1259058 Monday, July 8, 2019 Standard 9612030 302
    1146190 Tuesday, July 9, 2019 Standard 9501025 0
    1137298 Wednesday, July 10, 2019 Standard 9501025 2
    1255209 Thursday, July 11, 2019 Standard 9514795 562
    1137302 Friday, July 12, 2019 backup 9514795 12
    1255213 Saturday, July 13, 2019 Standard 9514795 17

     

    in excel I did it with adding 2 column for determining month and transaction status and have 2 pivot to get nominator and denominator and simply could have %Inactive Terminals values based on techniciansID.

     

    how could I do that in Power BI?

    thanks

    Thursday, September 19, 2019 7:44 PM

All replies

  • The code below makes the following assumptions:

    1) The month name is always the fifth column of the table
    2) The month name in the fifth column will be spelt using the full English name for the month
    3) The dates excluded from the InstallDate column are based on the month name in the fifth column
    4) The calculation returns a number. If you need to return a single row table, please advise of column name(s)

    If any of these assumptions are incorrect, please advise of the correct assumption(s)

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        changedType = Table.TransformColumnTypes(
            Source,
            {
                {"TerminalD", Int64.Type}, 
                {"InstallDate", type date}, 
                {"Group", type text}, 
                {"TechnicianID", Int64.Type}, 
                {"September", Int64.Type}
            }
        ),
        columnMonthName = Table.ColumnNames(changedType){4},
        filteredRows = Table.SelectRows(
            changedType, 
            each Text.Upper(Date.MonthName([InstallDate])) <> Text.Upper(columnMonthName) and 
            Text.Upper([Group]) <> "BACKUP"
        ),
        filteredRowsCount = Table.RowCount(filteredRows),
        inactiveRowsCount = Table.RowCount(Table.SelectRows(filteredRows, each Record.Field(_, columnMonthName) < 20)),
        percentInactive = Number.Round((inactiveRowsCount/filteredRowsCount) * 100, 2)
    in
        percentInactive

    Thursday, September 19, 2019 9:45 PM