Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

conditional sum in a filtered table

Hi all

from a DB, here the link, I made this summarize funcion:

Pick_PKuph = summarize(filter(PICK, PICK[TIPO]= "PK"),PICK[ORA], PICK[GIORNO], "tot", sum(PICK[TOTALE]), "Prel", sum(PICK[PRELEVATO]), "ops nr", DISTINCTCOUNT(PICK[MATRICOLA]), "Contenitori", count(PICK[CONTENITORE]))

 

How can we make a conditional sum of TOT while filtering:

- the sum of TOT for each ORA, by each MATRICOLA, must be > 7?

Here the result reached:

Nt tot 30847983364764837911740378025744545265380131
Nr ops 6131819141243137983129

 

In the attached link all data are shown and also an example of pivot table to achive the output

Thanks for help

Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

     

    Nr Ops := 
var a = SUMMARIZE(PICK,PICK[ORA],PICK[MATRICOLA],"Tot",sum(PICK[TOTALE]))
return COUNTROWS(filter(a,[Tot]>7)) 

Nt Tot := 
var a = SUMMARIZE(PICK,PICK[ORA],PICK[MATRICOLA],"Tot",sum(PICK[TOTALE]))
return SUMX(filter(a,[Tot]>7),[Tot])

     

    I did the PK filter on page level but you can add it to the measures if you want.

     

     

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

     

    Nr Ops := 
var a = SUMMARIZE(PICK,PICK[ORA],PICK[MATRICOLA],"Tot",sum(PICK[TOTALE]))
return COUNTROWS(filter(a,[Tot]>7)) 

Nt Tot := 
var a = SUMMARIZE(PICK,PICK[ORA],PICK[MATRICOLA],"Tot",sum(PICK[TOTALE]))
return SUMX(filter(a,[Tot]>7),[Tot])

     

    I did the PK filter on page level but you can add it to the measures if you want.