Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » PowerPivot » DAX-funkce-CALCULATE-Power-Pivot-Excel
Microsoft Excel logo

DAX - funkce CALCULATE - Power Pivot Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na funkci CALCULATE v DAX Power Pivot pro Excel. Spočítá výraz dle použitých filtrů. Více v praktických ukázkách v článku.

Úvodem do funkce CALCULATE v DAX

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

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ů).

Jak na funkci CALCULATE v DAX - data

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

Jak na funkci CALCULATE - ve filtru

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

Jak na funkci CALCULATE - ve filtru

Součty s filtry a možné chyby

Jak na funkci CALCULATE - ve filtru

Související funkce a články

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


Microsoft Excel DAX - stahuj logo

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

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

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

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

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







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |