Trikovi u Excelu 142. deo – Uporedite funkcije SUMIFS i CALCULATE

Uporedite funkcije SUMIFS i CALCULATE

PITANJE: Kakva je razlika između funkcija Sumifs i Calculate?

ODGOVOR: Pomoću funkcije SUMIFS idete kroz skup podataka i pronalazite redove koji odgovaraju svim zadatim kriterijumima.

    Pomoću funkcije CALCULATE idete kroz skup podataka i izračunavate vrednosti koje odgovaraju filterima u toj funkciji. ALI… tu postoji i spoljna sila koja primorava na primenu drugih filtera. Ti filteri možda proističu iz dugmadi za filtriranje ili čak i iz oznaka za redove i kolone. Kada PowerPivot izračunava ćeliju F4 na Slici 1, on mora da poštuje filter “dan=subota” u funkciji CALCULATE, ali i filter “mesec=januar” koji uslovljava oznaka reda u ćeliji D4 i filter “godina=2016″ koji uslovljava dugme za filtriranje.

    Da li ste spremni za nešto čudesno? Filteri u funkciji CALCULATE imaju sposobnost da narede toj spoljnoj sili da ne primeni određeni filter. Kada bi ta formula u ćeliji F4 koristila filter “mesec=februar”, taj filter u formuli CALCULATE nadjačao bi filter iz oznake reda u ćeliji D4. Pokazaću vam jedan primer.

    Pogledajte Sliku 1.

    Kolona E, “Zbir prihoda” (Sum of Revenue) je obično klasično polje kod kojeg sam uzeo polje prihoda iz liste polja i stavio ga u ciljnu zonu za vrednosti. Kolona E poštuje filtere u dugmetu za filtriranje i filtere oznaka redova u koloni D.

    Kolona F je DAX mera kod koje sam koristio funkciju CALCULATE da nadjačam filter za sektor. Bez obzira koja je oznaka u koloni D, DAX mera u koloni F filtriraće sektor “Odeća” (Apparel). Ali kolona F i dalje poštuje filter za mesec. Formula za meru u koloni F je:

=CALCULATE(SUM(Sales[Revenue]),Sector[Sector]=”Apparel”).

    Kolona G je DAX mera kod koje sam eliminisao filter sektora pomoću funkcije ALL. Svaki red u koloni G prikazaće zbir za sve sektore, iako oznaka reda u ćeliji D5 kaže da je taj red za kućne aparate (Appliance). Formula za meru u koloni G je:

=CALCULATE(SUM(Sales[Revenue]),All(Sector)). Imajte u vidu da ova formula i dalje poštuje filter primenjen u dugmetu za filtriranje meseci.

    Pravo korisno polje je kolona H. Ona uzima prihod za ovaj sektor i deli ga prihodom za sektor odeće. Ovde formula ponovo koristi postojeću DAX meru iz kolone F:

=sum(Sales[Revenue])/Sales[ApparelSector]. Naravno, ta formula i dalje poštuje filter meseca koji se primenjuje iz dugmeta za filtriranje.

    Kada promenite filtere osim sektora, ažuriraće se sve formule (Slika 2). Ovde je data ista pivot tabela filtrirana tako da prikazuje jun, jul i avgust.