Tweet

DAX - funkce CALCULATE - Power Pivot Excel

Úvodem do funkce CALCULATE v DAX

Pro přehlednost je článek rozdělen na jednotlivé kapitoly

• Syntaxe funkce CALCULATE
• Data pro funkci CALCULATE
• Praktické použití funkce CALCULTE - součet dle podmínek
• Pokročilé použití funkce CALCULTE - obrat dle podmínek pokud je jen počet a cena
• Kombinace s jinými funkcemi
• Jak funguje s filtry v kontingenční tabulce s využitím funkce CALCULATE
• Související funkce a články
• Ke stažení

Poznámka: Tato funkce funguje i v Power BI.

Syntaxe funkce CALCULATE

Vyhodnotí výraz na základě zadaných filtrů. Vrátí jednu hodnotu.

Syntaxe

CALCULATE(<expression>,<filter1>,<filter2>...)

Poznámka: Funkce v DAX se zapisují pouze anglicky, nemají český název.

Argumenty funkce:

• expression - Výraz, který se má vyhodnotit
• filter1, filter2,... - (Nepovinné) Čárkou nebo středníkem (dle verze Excel) oddělený seznam logických výrazů nebo výrazů tabulky, který definuje filtr. V rámci filtru, lze využít || pro OR (nebo)

Pozmámky:

Logické výrazy (expression) mají pár omezení:

• Výraz nesmí odkazovat na výpočtovou míru (meausuare)
• Výraz nezvládne vloženou funkci CALCULATE
• Výraz nezvládne funkce na prohledávání tabulky, ale lze použít libovolnou která vyhledá jednu, nebo počítá skalární hodnotu
• lze využít || pro nebo (například výrobek jedna, nebo výrobek 2)

Data pro funkci CALCULATE

Pro praktické ukázky použití funkce CALCULATE v DAX mám v Power Pivot k dispozici tabulku, která se jmenuje Výrobky. Navíc obsahuje jedničkový vektor, neboli sloupec, které obsahuje v každém řádku hodnotu jedna, ať se dají ukázat další možností a možné komplikace, které mohou nastat, pokud nevhodně funkci použijete (bezmyšlenkovité kopírování hotových ukázkových DAX vzorců).

Tabulky jsou záměrně malé, ať si člověk může ručně zkontrolovat, zda je spočtený počet v řádku v DAX správný. Případně si můžete dále s touto funkcí hrát, za využití dalších funkcí (COUNTROWS, FILTER, SUMX, ...).

Praktické použití funkce CALCULATE - obraty

Potřebuji spočítat obraty (mám k dispozici sloupec obraty), kdy ve vypočtené míře potřebuji hodnotu dle filtru.

Celkový součet

Začneme tím, že si spočteme celkový obrat nad sloupcem Obrat v tabulce Výrobky. Využijeme funkci SUMX:

Obrat s CALCULATE a SUMX:=CALCULATE( SUMX('Výrobky';'Výrobky'[Obrat]) )

To stejné jen s funkci SUM, máme výhodu, že počítáme konkrétní sloupec

Obrat s CALCULATE a SUM:=CALCULATE( SUM('Výrobky'[Obrat]) )

Poznámka: Vyzkoušejte v kontingenčních tabulkách jak se zachovají míry při doplnění filtru (slicers).

Celkový součet - výpočet

Pokud chci obrat, ale musím pronásobit cena za ks a počet kusů.

Obrat Výrobek1 cena * pocet ks:=CALCULATE( SUMX('Výrobky';'Výrobky'[Cena]*'Výrobky'[Počet ks]))

Součet ale budu filtrovat

Součet ale potřebuji jen pro výrobek s názvem "Výrobek 1", aplikuji filtr nad sloupcem Produkt. Zde vím jak se konkrétní výrobek jmenuje:

Obrat Výrobek1 _ ver1:=CALCULATE( SUMX('Výrobky';'Výrobky'[Obrat]) ;'Výrobky'[Produkt]="Výrobek 1" )

Chci filtrovat Výrobek 1 a dle dalšího sloupce barvu bíla.

Obrat Výrobek1 a bílý:=CALCULATE( SUMX('Výrobky';'Výrobky'[Obrat]) ;'Výrobky'[Produkt]="Výrobek 1" ; 'Výrobky'[Barva] = "Bíla")

Chci jeden nebo druhý výrobek. Musím využít NEBO/OR, což v DAX lze zapsat || dvě svislé čárky.

Obrat Výrobek1 a Výrobek2 _ ver1:=CALCULATE( SUMX('Výrobky';'Výrobky'[Obrat]) ;'Výrobky'[Produkt]="Výrobek 1" || 'Výrobky'[Produkt]="Výrobek 2")

Pokud mám k dispozici cenu za jeden kus a počet kusů a navíc chci filtr na konkrétní produkt (Výrobek 1).

Obrat Výrobek1 _ver2:=CALCULATE( SUMX('Výrobky';'Výrobky'[Cena]*'Výrobky'[Počet ks]) ;'Výrobky'[Produkt]=""Výrobek 1"" )

Pokročilé použití funkce CALCULATE

Potřebuji zjistit počet řádků (u obratu to je podobné), záměrně, protože těch pár řádků člověk vidí spočtených na první pohled. Navíc záměrně je v tabulce i sloupec pomoc (já tomu říkám jedničkový vektor), který je pro kontrolu, každý řádek se v kontingenční tabulce spočte jednou.

Určitě vás napadne využít funkci COUNTROWS a filtr, což na první pohled a při správném použití v kontingenční tabulce funguje, ale zkuste si v kontingenční tabulce metriku doplnit o dimenzi, třeba oněch názvů výrobků (podle měst atd.):

Počet řádku Výrobek 1 _ v1:=CALCULATE( COUNTROWS('Výrobky') ;'Výrobky'[Produkt] = "Výrobek 1" )

Co když počet řádků doplníme o funkci FILTER? Opět co se stane v kontingenční tabulce? Zkoušejte si a hrajte si s výpočty:

Počet řádku Výrobek 1 _ v2:=CALCULATE( COUNTROWS('Výrobky'); FILTER( VALUES( 'Výrobky'); 'Výrobky'[Produkt] = "Výrobek 1" ) )

Kontingenční tabulka - zobrazení

Ukázka co vytvoří výpočty v kontingenční tabulce

Rozšíření této kapitoly je v přípravě.

Kombinace s jinými funkcemi

Tato kapitola je v přípravě.

Funkce CALCULATE s filtry v KT

Metriku obratu a počtu řádku určitě budete využívat v kontingenčních tabulkách. V případě filtrování, které máte přímo ve funkci se můžete dostat do stavu, kdy se zmatete, například když k metrice přidáte dimenzi (jak jste zvyklý z klasických kontingenčních tabulek v Excel.

Součty

Součty s filtry a možné chyby

Související funkce a články

Související články s funkcí ALL:

• Jak vložit tabulku (data) do Power Pivot
• Úvod do DAX
• Funkce SUM vs SUMX

Ke stažení

Soubor ke stažení zdarma je v přípravě. Podpořit zveřejnění můžete podporou na Patreon.

Závěrem

Narazili jste na nějaké problémy, máte tip, můžete se zmínit v komentářích.

Článek byl aktualizován: 12.11.2020 08:29

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.