The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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 |
---|---|
62 | |
59 | |
54 | |
51 | |
33 |
User | Count |
---|---|
179 | |
87 | |
70 | |
48 | |
45 |