create a summarized table with unique calculated values.

[newpi]

I have a table as follows.

account ID	computer name	computer version	available or not (1 or 0 values)
123	F1	1.1	1
123	F1	1.0	1
123	F2	1.1	1

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 ID	computer name	total computers available by acctid
123	F1	2
123	F2	2

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

[amitchandak]

@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]

@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 · Accepted solution]

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.