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 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 c...
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.

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.