Pred časom sa na mňa obrátil jeden z čitateľov tohto blogu s otázkou, ako sa robí analýza predaja položiek tovaru z registračných pokladníc, resp. z predajných transakcií, kde sa v jednej transakcii predáva viacero druhov tovaru. Konkrétne, ako zistiť konkrétne vzťahy medzi konkrétnymi druhmi predaných položiek. Pozrime sa teda na to, ako sa s tým jednoducho popasovať.

Zadanie príkladu aj vzorové dáta som mierne pozmenil kvôli anonymite čitateľa. Povedzme, že máme takúto jednoduchú tabuľku s transakciami a tovarom, ktorá bežne vychádza z výstupu registračných pokladníc:

Tabuľka sa volá Transakcie. A býva bežným vstupom do toho, čomu sa hovorí analýza predaja položiek tovaru. V tejto tabuľke je na každom riadku jedna zakúpená položka. Jednotlivé stĺpce majú nasledovný význam:

1. ID_riadku – jednoznačný identifikátor riadku (pre tento príklad len informačný stĺpec)
2. ID_dokladu – jednoznačný identifikátor jedného nákupu – tzn. dokladu, bločku, transakcie, a pod.
3. Nazov_tovaru – názov tovaru pre danú predajnú položku
4. Suma – predajná cena položky v eurách
5. Datum – dátum predaja

Z tejto tabuľky budeme potrebovať zodpovedať nasledujúce otázky pomocou kontigenčnej tabuľky a PowerPivotu, resp. Power BI:

1. koľko je dokladov, v ktorých je iba notebook?
2. koľko je dokladov, v ktorých je notebook spolu s nejakou ďalšou položkou?
3. koľko je dokladov, v ktorých nie je notebook?

Prvú otázku nám vyrieši nasledovné merítko, ktoré pochopíte po menšej dávke kofeínu 🙂 Jeho miernou úpravou potom vyriešite ľahko aj zvyšné otázky.

Alebo, ak chcete kopírovateľný text, tak tu je nenaformátovaná obluda ešte raz:

Pocet iba s notebookom:=COUNTROWS(FILTER(SUMMARIZE(Transakcie; [ID_dokladu]; “Pocet poloziek v doklade”; COUNTROWS(Transakcie); “Pocet riadkov s notebookom”; COUNTROWS(FILTER(Transakcie; [Nazov_tovaru] = “Notebook”))); [Pocet poloziek v doklade] = 1 && [Pocet riadkov s notebookom] = 1))

A ako to funguje? Jednoducho 🙂

V prvom kroku použijeme funkciu SUMMARIZE, ktorá nám zoskupí riadky v danom kontexte podľa ID dokladu. Ku každému dokladu navyše vypočíta dva stĺpce – “Pocet poloziek v doklade” (obsahuje počet položiek v celom doklade) a “Pocet riadkov s notebookom” (zistí, koľko riadkov v danom doklade obsahuje notebook). Všimnite si, že vo vzorcoch pre tieto dva stĺpce používam názov celej tabuľky. V skutočnosti sa to nepočíta za celú tabuľku, ale len za aktuálny kontext výpočtu vo funkcii SUMMARIZE, ktorým sú riadky z tej tabuľky prislúchajúce danému dokladu. Netreba explicitne uvádzať, že chcete riadky len za daný doklad, funguje to automaticky vďaka kontextu funkcie.

Keby ste spustili iba túto funkciu SUMMARIZE nad našou tabuľkou, tak dostanete takýto výstup:

Problém je však v tom, že je to tabuľka, a my z nej potrebujeme dostať jedno číslo ako odpoveď na našu pôvodnú otázku. Zavoláme teda nad touto virtuálnou tabuľkou funkciu FILTER s podmienkou “[Pocet poloziek v doklade] = 1 && [Pocet riadkov s notebookom] = 1”, ktorá zabezpečí, že sa z tejto tabuľky odfiltrujú iba riadky, kde je v oboch vypočítaných stĺpcoch iba číslo 1. Pretože sme chceli vedieť, koľko je dokladov, v ktorých je iba notebook. Čiže, technicky povedané, koľko je dokladov, kde je iba 1 položka resp. riadok, a zároveň majú iba jeden riadok s notebookom. A keďže výsledkom funkcie FILTER je znova tabuľka, tak tieto riadky zosumarizujeme funkciou COUNTROWS, ktorá vracia počet riadkov zo zadanej tabuľky. A to je presne to, čo sme chceli vedieť 🙂

Druhú otázku vyriešime miernou úpravou podmienky vo funkcii FILTER takto:

“[Pocet poloziek v doklade] > 1 && [Pocet riadkov s notebookom] >= 1”. Čiže celý vzorec bude:

No a tretiu otázku vyriešime podobne – upravíme podmienku vo funkcii FILTER takto:

“[Pocet poloziek v doklade] >= 1 && [Pocet riadkov s notebookom] = 0”. Vzorec bude takýto:

No a keď si z toho spravíte kontingenčku (ideálne neskôr prepojenú aj s časovou tabuľkou, aby ste mohli robiť časovú analýzu), tak Vaša analýza predaja položiek tovaru bude vyzerať takto:

Takže, po troche básnického zamyslenia nie je problém vypočítať aj takéto ukazovatele 🙂 V ďalšom článku si ukážeme, čo sa ešte dá z takejto tabuľky vypočítať. Ak ma teda dovtedy neprejde električka alebo nepadne na mňa lietadlo. Dovtedy píšte, mailujte a volajte, čo by ste chceli vyriešiť 🙂

Michal Chmelár

Autor, tréner a expert na PowerPivot, Power BI a jazyk DAX. Založil som tento web, aby som pomohol dostať PowerPivot a Power BI do širšieho povedomia, a aby som ľuďom ukázal, že aj komplexné analytické problémy idú riešiť jednoducho. Po nociach vzývam Majstra Yodu a tajne plánujem ovládnutie vesmíru.