Forum Discussion

newpi's avatar
newpi
Helper V
5 years ago
Solved

create a summarized table with unique calculated values.

I have a table as follows.

 

account IDcomputer namecomputer versionavailable or not (1 or 0 values)
123F11.11
123F11.01
123F21.11

 

 

I want to create a calculated table for which I used the summarize function and it got me unique values of account id and computer name. But when I sum the availability column, its summing the F1 computer twice as its a duplicate in the original table. Bascially I want to ignore the computer version column and only sum by account id column.

Output should be :


acct IDcomputer nametotal computers available by acctid
123F12
123F22

 

 

I have many columns similar to available or not that I need to summarize.

Need Help
Show and Tell
Tips and Tricks
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi newpi ,

     

    Try this:

    Table 2 =
SUMMARIZE (
    'Table',
    'Table'[account ID],
    'Table'[computer name],
    "total computers available by acctid",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[computer name] ),
            ALLEXCEPT ( 'Table', 'Table'[account ID] )
        )
)

    Sample .pbix

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    newpi , with data you shared F2 will not come 2. May be you have more data

     

    In a visual if use the below measure with acct ID, computer name

    Sum(Table[available])

    It should work

     

    Or a new table

    summarize(Table, Table[acct ID], Table[computer name],"Sum available",Sum(Table[available]) )

    • newpi's avatar
      newpi
      Helper V
      5 years ago

      amitchandak F2 is a typo. Should be 1 . But, I've tried Summarize same as your formula and that didn't work. I want to create a table so that I can use columns as a filter on page. Also, that table has date so if I can use Max(Date) or something to take only max of the values but dint figure out the formula.

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        5 years ago

        Hi newpi ,

         

        Try this:

        Table 2 =
SUMMARIZE (
    'Table',
    'Table'[account ID],
    'Table'[computer name],
    "total computers available by acctid",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[computer name] ),
            ALLEXCEPT ( 'Table', 'Table'[account ID] )
        )
)

        Sample .pbix

         

        Best Regards,
        Liang
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.