Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

need row count and sum combination

Hi

i have below rows need last column (row_count) as result that is

if system is a or b i need to do row count based on type if system is c i need to add rowcnt 

can any one help me in this

system   type    rowid      rowcnt        row_count
a              x           11                                22
a              y           22                                32
b              x           13                                22
b              y           32                                32
c              x            111            20              22
c               y            212           10              32

c               y            212           20              32

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Check the formula.

    Column = IF('Table'[system]="a"||'Table'[system]="b",CALCULATE(MAX('Table'[rowid]),FILTER(ALLEXCEPT('Table','Table'[type]),'Table'[system]="a"||'Table'[system]="b")),IF('Table'[system]="c",CALCULATE(MAX('Table'[rowid]),ALLEXCEPT('Table','Table'[type])))+'Table'[rowcnt])

    Result would be shown as below.

     

     

    Best Regards,

    Jay

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Either something like the above or add some columns on or detail records

    typeXcount: IF (system = a or system = b) and type= x then 1 else 0 

    typeYcount: IF (system = a or system = b) and type = y then 1 else 0

    typeCcount: IF (system = c) then 1 else 0)

     

    In your visual you can then just sum the columns

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , logic is not clear with the data you have shown.

     It seem like this measure

    calculate(counrows(Table), allexcept(Table, Table[Type]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Check the formula.

    Column = IF('Table'[system]="a"||'Table'[system]="b",CALCULATE(MAX('Table'[rowid]),FILTER(ALLEXCEPT('Table','Table'[type]),'Table'[system]="a"||'Table'[system]="b")),IF('Table'[system]="c",CALCULATE(MAX('Table'[rowid]),ALLEXCEPT('Table','Table'[type])))+'Table'[rowcnt])

    Result would be shown as below.

     

     

    Best Regards,

    Jay