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

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

SUM a SUMX v Power Pivot DAX

Videokurzy Excel

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

Jak na funkce SUM a SUMX. Jaký je rozdíl proč je tam to X a proč někdy vyjdou výsledky stejně.

Předpokladem je mít k dispozici v Excel aktivovaný doplněk Power Pivot.

Poznámka: Velice podobně fungují DAX funkce i v Power BI, ale já se specializuji na Excel, tak nepolezu do zelí kolegům, kteří se specializují na Power BI ;)

Úvodem do SUM a SUMX

V tomto článku se podíváme na dvě podobné funkce které se hojně využívají (dají využít) v Power Pivot (rozšíření, které je součástí Excel) při psaní DAX funkcí. Z důvodu přehlednosti a možnosti doplňování dalších příkladů a ukázek je článek rozdělen na jednotlivé kapitoly:

Power Pivot DAX Excel - ukázka dat ;)

Trocha teorie - rozdíl SUM SUMX

Proč existují dvě funkce? Protože DAX (Data Analysis Expressions) a Power Pivot je pokročilejší nástroj a jednoduchá agregační SUM funkce by nestačila (nebo stačila, ale pro složitější výpočty by byly komplikovanější, kdyby nebylo agregačních funkcí, které umí iterovat - sčítat po řádcích).

Neboli:

  • SUM - klasická funkce SUM (v češtině v klasickém Excel SUMA), ale když se na syntaxi této funkci podíváte v DAX pod Power Pivot uvidíte pouze jeden parametr - ColumnName neboli název sloupce. Tím pádem můžete sčítat jej všechny položky daného sloupce. Ale vy často potřebujete dělat výpočty, kdy potřebujete sčítat sloupce samostatně na základě nějaké podmínky. Proto existuje i SUMX.
  • SUMX - jde také o agregační funkci, ale s možností iterace, neboli jinak řečeno funkce zpracuje samostatně výraz na řádku (pro danný řádek - interuje) a navíc se nad jednotlivými řádkovými výpočty provede agregace (ona SUMA). A ve spojení s dalšími funkcemi můžeme ve výpočtech využívat podmínek, atd.

Vše si prakticky ukážeme, což je z mého pohledu jasnější než pustá a "nudná" teorie.


Syntaxe SUM a SUMX

V této kapitole se podíváme na syntaxi funkcí SUM SUMX. Doporučuji Excel Power Pivot používat v angličtině, ale kdyby náhodou někdo musel využívat i v češtině, tak argumenty popisují v obou jazycích:

Syntaxe SUM

=SUM(ColumnName) =SUM(NázevSloupce)

Argumenty

ColumnName - Název sloupce - Název sloupce nad kterým se má provést agregace, neboli sloupec, který se má sečíst, doporučuji označovat i tabulku nad kterou se součet bude provádět

Syntaxe SUMX

=SUMX(Table;Expression) =SUMX(Tabulka;Výraz)

Argumenty

Table - Tabulka - tabulka nad kterou se bude agragace (výpočet) provádět

Expression - Výraz - Název sloupce, případně výpočet, který se bude iterovat provádět po řádcích a "řádkové" výpočty se nakonec agregují

Než nudná teorie pojďme se podívat na funkce prakticky.


Data, která budeme používat

1) Než se dáme do výpočtu budeme potřebovat data.

2) Tabulky převést na tabulka jako tabulka a vložit do Power Pivot pod názvem Výrobky. Viz článek jak dostat data do Power Pivot.

V základu budeme mít jednoduchou tabulku a následně i pokročilejší (mezi tabulkami budou relace).

První tabulka

Tabulky jsou ke stažení.

Prodejce Produkt Cena Počet ks Obrat
A Výrobek 1 1000 4 4000
B Výrobek 2 500 1 500
.. ..

Ukázka:

Power Pivot DAX Excel - ukázka dat ;)

Druhá tabulka

V přípravě

Základní použíti SUM SUMX

Provedeme součet sloupce Obrat - ať víme, jak si stojí "demofirma". Zde si ukážeme že SUM a SUMX mohou dávat stejné správné výsledky. Předpokládám, že tabulku již máte v Power Pivot.

Pak stačí do measuere (míry) vepsat DAX vzorec za využití funkce SUM:

Součet celkem:=SUM([Obrat])

Lepší je i u funkce SUM uvádět název tabulky:

Součet celkem2:=SUM('Výrobky'[Obrat])

Pak stačí do measuere (míry) vepsat DAX vzorec za využití funkce SUMX:

Součet celkem SUMX:=SUMX('Výrobky';'Výrobky'[Obrat])

Základ máme za sebou. Můžeme si malinko zkomplikovat, "zesložitit".

Pokročilejší použití SUMX - výpočet nad sloupci

Co ale když mám jednoduchou otázku: "Chtěl bych prodejcům vyplatit odměnu 10% z obratu." Nechci si do tabulky přidávat další výpočtový sloupec, ale chci mít výpočet rovou:

Stačí použít funkci SUMX

Odměna 1:=SUMX('Výrobky';'Výrobky'[Obrat]*,1)

Ono někdo může využít funkci a výsledek vynásobit 0,1 což opět vyjde správně:

Odměna 2:=SUM('Výrobky'[Obrat])*,1

Už cítíte, když dáme výpočet ze dvou sloupců, nebo podmínku, tak tam bude problém...

Začneme výpočtem se dvěma sloupci.

Potřebuji vypočíst Celkem obraty, ale mám k dispozici pouze sloupce Cena a Počet ks. Což není s využitím funkce SUMX problém (ta iterační výpočty nad řádky zvládá):

Součet obrat 1:=SUMX('Výrobky';'Výrobky'[Cena]*'Výrobky'[Počet ks])

Pokud někdo udělá fatální chybu a použije funkci SUM, kdy sečte sloupec Cena a vynásobí funkci SUM sečtenou nad sloupcem Počet ks. Získá chybné číslo!

! Chybně ! Součet obrat chybně:=SUM('Výrobky'[Obrat])*SUM('Výrobky'[Počet ks])

Neboli nejprve došlo k agregaci počtu kusů (provedl se součet nad sloupcem) a agregaci cen a tyto údaje se pronásobily! Což je špatně! S pár výrobky v ceně pár stovek nemůžete mít obrat 0,5 miliónu!!!

Takže správně je využívat funkci SUMX. Tak a teď do toho zakomponujeme "jen" podmínku.


Podmínky ve výpočtech

Půjde pouze o zmínku o funkcích CALCULATE a FILTER, které k podmínkovému výpočtu použiji. Podrobněji funkce proberu v samostatných článcích, ale proč nemít ukázku i u funkce SUMX (kde se krásně hodí):

Úkol 1

Potřebuji obraty jen pro: Výrobek 1. Jak na to využitím FILTER?:

Obrat Výrobek1 _verA:=SUMX(FILTER('Výrobky';'Výrobky'[Produkt]="Výrobek 1");'Výrobky'[Obrat])

Nebo pokud nemám sloupec obrat k dispozici. Najednou už je vzoreček trochu velký co?

Obrat Výrobek1 _verB:=sumx(filter('Výrobky';'Výrobky'[Produkt]="Výrobek 1");'Výrobky'[Cena]*'Výrobky'[Počet ks])

Tip mohu využít při psaní mezery a Alt + Enter pro nový řádek.

Výpočet s využitím funkce CALCULATE a grafického rozdělení z důvodů přehlednosti (Alt + Enter pro nový řádek a mezerník)

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

Nebo pokud máte jen Počet a cenu:

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

Další ukázky buodu následovat. Pokud nechcete čekat můžete zkoušet sami a do komentářů se pochlubit jak se daří ;)

Více tabulek s relacemi a výpočty

V přípravě. Zde už předpokládám, znalost relací atd. Doplním na základě případných dotazů, nebo po vytvoření vhodných a "prezentovatelných" tabulek.


Chyby ve funkcích a jejich hledání

Při tvorbě DAX funkci se vám budou objevovat i chybové hodnoty (špatně napsaný vzorec). Zapomenutý střední, špatně napsán název sloupce atd. Proto při psaní využívám trik do vzorců vkládám mezery a přes Alt + Enter do vzorce vyložím nový řádek. Následně vzorec vypadá např:

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

Což z mého úhlu pohledu, vypadá lépe a přehledněji (ale klidně si vzorce nechejte na jednom řádku, fungují v obou případech):

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

Ono se ve vzorci udělá rychle chyba a pak ji najděte... No když jste dyslektik jako já ... Provnejte dva vzorce a najděte v nich chybu:

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

Máte? Pokud hned super. Pokud ne zkuste tu chybu najít ve druhé funkci

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

Máte? Jednodušší? Jasně v SUMX něco chybí.

V tom druhém vidíte hned, že v SUMX chybí argument tabulka v tom prvním to hned nedám (musím tomu věnovat několik minut). Ale je to na vás co je pro vás přehlednější používejte.

O chybách a tvorbě a úpravě vzorců se budu ještě věnovat podrobněji v některém z dalších článků.


Související články

Články ohledně Power Pivot, která jsem již sepsal a pá která na sepsaní čekají:


Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor DAX SUM a SUMX - praktické příklady - Excel Power Pivot soubor ve formátu *.xlsx ke stažení zdarma. Soubor využívá doplněk Power Pivot.


Závěrem

Máte nějaké příklady a tipy na použití funkci SUM a SUMX v Power Pivot, rád do článku postupně doplním.

Článek byl aktualizován: 09.02.2019 17:13

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 - 2019 | 838

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.