Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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] )
)
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@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]) )
@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.
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] )
)
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
120 | |
72 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |