Kontingenčné tabuľky sú skvelý nástroj na bežnú analýzu dát. V niektorých prípadoch sa však nezobrazujú tak, ako by ste presne chceli, a možnosti ich nastavenia sú tiež relatívne obmedzené. Ak chcete napríklad zobraziť celkový súčet na začiatku kontingenčnej tabuľky, či už v Power BI alebo excelovskom PowerPivote, tak máte smolu – nepodporujú to. Na všetky problémy však existuje riešenie, a my sa teraz na jedno z nich pozrieme.

Prečo zobraziť celkový súčet na začiatku kontingenčnej tabuľky

Na ukážku aj riešenie si opäť zoberieme náš vzorový súbor PowerPivotu, resp. Power BI.

Spravíme si kontingenčku, kde:

1. do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky,
2. do oblasti riadkov dáme stĺpec CalendarYear z tabuľky Čas,
3. do oblasti stĺpcov dáme stĺpec MonthName z tabuľky Čas.

Výsledná kontingenčka bude vyzerať takto:

Kontingenčka v stĺpoch zobrazuje Obrat po jednotlivých mesiacoch v daných rokoch (ktoré sú na riadkoch). Potiaľto fajn. Problém nastáva vtedy, keď chceme vidieť celkový súčet za daný rok. To už musíme odscrollovať kontingenčku smerom doprava úplne na koniec, kde tie súčty uvidíme:

Toto je však dosť nepohodlné, keď potrebujete opakovane porovnávať čísla v prvých mesiacoch s celkovým súčtom. Prípadne často potrebujete scrollovať len kvôli tomu celkovému súčtu. Preto by bolo lepšie zobraziť celkový súčet na začiatku kontingenčnej tabuľky.

Kde začať

Riešenie je celkom jednoduché, a časom sa vám bude javiť možno až triviálne. V prvom rade si potrebujeme spraviť v dátovom modeli ďalšiu tabuľku. Pretože keď chceme do kontingenčky doplniť ďalšiu položku do riadku alebo stĺpca, tak ju buď musíme doplniť do toho stĺpca v podkladovej tabuľke, ktorý sme dali v kontingenčke do oblasti riadkov/stĺpcov. Alebo vytvoriť taký stĺpec v dátovom modeli, ktorý bude obsahovať všetky unikátne položky z pôvodného stĺpca, plus tú novú položku, a takýto stĺpec vhodne použiť v kontingečke. Z praktických dôvodov je lepšie druhé riešenie, a preto vytvoríme novú vypočítanú tabuľku.

Táto tabuľka bude obsahovať všetky položky zo stĺpca MonthName, ktorý sme použili v oblasti stĺpcov kontingenčky, a kam chceme doplniť položku “Celkovo” na začiatok pred všetky mesiace. Samozrejme bude obsahovať navyše tú položku s názvom “Celkovo”, ktorá nám po použití tohto stĺpca v kontingenčke vytvorí stĺpec s takouto položkou. Takáto tabuľka sa dá vytvoriť napr. v Power Query, ale aj ako vypočítaná tabuľka, či už v Power BI, alebo v PowerPivote. My ju vytvoríme ako vypočítanú tabuľku s názvom “Mesiace a celkovo”, s týmto vzorcom:

Mesiace a celkovo = UNION(ALL('Čas'[MonthName]; 'Čas'[MonthNumberOfYear]); 
                          ROW("MonthName"; "Celkovo"; "MonthNumberOfYear"; 0))

Dáta v takejto tabuľke budú vyzerať takto:

Tabuľka je kvôli prehľadnosti zoradená podľa druhého stĺpca. Pre riešenie to nie je nutné spraviť, je to len aby ste to lepšie pochopili. V tomto prípade si najprv funkciou ALL dáme vrátiť všetky unikátne dvojice s názvom mesiaca a číslom mesiaca. Potom funkciou ROW vytvoríme jednoriadkovú tabuľku, kde v stĺpci MonthName bude hodnota “Celkovo” a v stĺpci “MonthNumberOfYear” bude hodnota 0. Tieto dve tabuľky potom zlepíme dokopy funkciou UNION, a vznikne z toho hore uvedená tabuľka.

Ďalšie úpravy

V ďalšom kroku nastavíme, aby položky zo stĺpca MonthName z tejto tabuľky, boli vo výslednej kontingenčke zoradené podľa stĺpca MonthNumberOfYear, tiež z tejto tabuľky. To spravíme pomocou funkcie “Zoradiť podľa stĺpca”, či už v PowerPivote, alebo v Power BI.

Potom prepojíme túto tabuľku s tabuľkou Čas, cez stĺpce MonthName. Čiže v tabuľke “Mesiace a celkovo” prepojíme stĺpec “MonthName” s tabuľkou “Čas” a stĺpcom “MonthName”:

Potom upravíme kontingenčku. Z oblasti stĺpcov vyhodíme pôvodný stĺpec MonthName, a namiesto neho tam pridáme stĺpec MonthName z tejto novej tabuľky. Priebežný výsledok bude vyzerať takto:

Ako zobraziť celkový súčet na začiatku kontingenčnej tabuľky

Na prvý pohľad sa síce nič nezmenilo, ale keď zapneme zobrazovanie prázdnych stĺpcov v kontingenčke, tak sa nám hneď v prvom stĺpci zobrazí naša nová položka “Celkovo”:

Tá je samozrejme zatiaľ prázdna, pretože v dátovom modeli jej neprislúchajú žiadne dáta. To však nevadí, pretože sa to dá vyriešiť napríklad jednoduchým IF-om v takomto novom merítku:

Obrat aj s polozkou Celkovo := 
IF( SELECTEDVALUE ( 'Mesiace a celkovo'[MonthName] ) = "Celkovo"; 
    CALCULATE ( [Obrat]; 
                ALL ( 'Mesiace a celkovo'[MonthName]; 
                      'Mesiace a celkovo'[MonthNumberOfYear] )
              ); 
    [Obrat] )

Toto merítko je veľmi jednoduché. Otestuje, či je v aktuálne počítanom stĺpci kontingenčky použitá položka “Celkovo”. Ak je, tak spočíta Obrat za všetky mesiace (viac detailov v tomto článku), inak spočíta klasický Obrat.

Z kontingenčky potom stačí už len vyhodiť pôvodné merítko Obrat, a namiesto neho tam dosadiť toto nové merítko. Výsledná kontingenčka nám už zobrazí celkový súčet za všetky mesiace v stĺpci “Celkovo” na začiatku:

Na záver

Takto jednoducho sa dá teda zobraziť celkový súčet na začiatku kontingenčnej tabuľky, či už v Power BI, alebo v excelovskom PowerPivote. Podstatou bolo využiť, ako funguje kontingenčná tabuľka, pridať vhodnú tabuľku a merítko do dátového modelu, a celé to pospájať konkrétnym spôsobom. Čo je pri troche predstavivosti a cviku celkom hračka 🙂