Ako vykresliť graf v tabuľke v reporte Power BI

V starších článkoch som ukázal, ako sa dajú zobraziť ikonky v tabuľkách – či už cez obrázky, alebo cez text. Tá prvá metóda je dokonca spomenutá aj v mojej knihe o Power BI. Čo však, ak chcete vykresliť graf v tabuľke či kontingenčke? V tom prípade na to treba ísť inak.

Oficiálne Power BI nedovoľuje vkladať jeden vizuál do druhého. Čiže nepôjde to spraviť vložením grafu do tabuľky. Nie je to však “marný”, pretože na záchranu nám prichádza fičúra, ktorá bola uvedená presne pred rokom, a ktorú tím Power BI oprášil minulý mesiac spolu s uvedením podpory pre ikony v podmienenom formátovaní. Tou fičúrou je možnosť pracovať s jazykom SVG, a vykresľovať vektorovú grafiku pomocou stĺpcov a merítok (!). A napriek tomu, akokoľvek komplikovane to znie, tak je to celkom ľahké.

Objavujeme čaro SVG

SVG je skratka zo “Scalable Vector Graphics”, a ide o jeden z podštandardov jazyka XML, kam patrí napr. aj jazyk HTML. Tento jazyk SVG slúži na vykresľovanie vektorovej grafiky – čiže čiar, kruhov, obdĺžnikov, polygónov a textu – ktoré sú nekonečne ostré, keď ich akokoľvek zväčšíte. V našom prípade SVG využijeme ako alternatívny vykresľovací systém, pomocou ktorého si nakreslíme, čo len budeme chcieť.

Podpora pre SVG v merítkach bola uvedená už vo vydaní August 2018. Pre stĺpce už niekedy predtým. A oficiálne až s podporou ikon v podmienenom formátovaní v júli 2019. Problém s tou podporou v podmienenom formátovaní je ale v tom, že ide o nedorobok, ktorý trpí rovnakým problémom ako klasické podmienené formátovanie v Power BI – neaplikuje sa na medzisúčty a celkové súčty. Preto na to budeme musieť ísť po starom.

Využijeme pri tom techniku, ktorú spomínam v tomto článku aj v knihe. Táto technika sa používa na zobrazovanie obrázkov v tabuľkách aj kontingenčkách, a ako jediná zo všetkých dostupných funguje aj na celkové súčty medzisúčty. Len namiesto URL adresy obrázkov použijeme inline SVG, čiže text v jazyku SVG, ktorý chceme vykresliť. Je to veľmi málo známa technika, ale veľmi užitočná v pokročilom reportingu.

Ukážeme si to opäť na našom vzorovom súbore Power BI. Spravme si na demonštráciu takúto jednoduchú kontingenčku:

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

Výsledok bude vyzerať takto:

A teraz by sme v ďalšom stĺpci v kontingenčke chceli zobraziť stĺpcový graf, ktorý zobrazí priebeh obratov za jednotlivé mesiace v roku. S tým, že aj keď nie sú obraty za niektoré mesiace, tak nech sú rovnaké mesiace v rôznych rokoch presne pod sebou, pre ľahké porovnanie.

Vytvoríme si teda takéto merítko, kde ten graf vykreslíme v jazyku SVG:

Graf =
VAR maximalnyObrat =
    MAXX ( VALUES ( 'Čas'[MonthNumberOfYear] ); [Obrat] )
RETURN
    "data:image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>"
        & CONCATENATEX (
            VALUES ( 'Čas'[MonthNumberOfYear] );
            "<line x1='"
                & (
                    CALCULATE ( MAX ( 'Čas'[MonthNumberOfYear] ) * 8 + 2 )
                ) & "' y1='" & ( 100 - ( [Obrat] * 100 / maximalnyObrat ) ) & "' x2='"
                & (
                    CALCULATE ( MAX ( 'Čas'[MonthNumberOfYear] ) ) * 8 + 2
                ) & "' y2='100' stroke='blue' stroke-width='6' />";
            "";
            'Čas'[MonthNumberOfYear]; ASC
        ) & "</svg>"

Ako to funguje

Na začiatku vzorca si do pomocnej premennej maximalnyObrat vypočítame maximálny obrat zo všetkých mesiacov v danom roku, kvôli normalizácii vykresľovaných hodnôt v grafe. V hlavnej časti vzorca začíname s textovou inštrukciou “data:image/svg+xml;utf8,”, ktorá hovorí reportu Power BI, že v danom poli bude nasledovať SVG grafika, zakódovaná v kódovaní UTF-8. Ak by ste už mali SVG dáta zakódované v Base 64, tak namiesto “utf8” tam dajte “base64”.

Potom nasleduje samotné SVG. V našom prípade začneme s tagom “<svg>”. Ak už ale máte hotové SVG vo formáte Base 64, tak tam tam namiesto toho rovno vylejte ten blob zakódovaný do Base 64.

V tagu <svg> je potrebné uviesť menný priestor, a vlastnosť “viewBox”. V nej uvádzate veľkosť okna, do ktorého budete vykresľovať grafiku.

Za tagom <svg> už nasledujú príkazy jazyka SVG, čiže konkrétne XML elementy, ktorými vykresľujete grafiku. V našom prípade použijeme element “<line>”, ktorý slúži na vykresľovanie čiar. A dostatočne hrubá čiara bude vyzerať ako stĺpec v grafe 😀 Túto čiaru vygenerujeme pre každý mesiac v danom roku, na základe obratu v danom mesiaci. Plus ten obrat znormalizujeme na hodnotu 0 až 100, na základe maximálneho obratu v roku (kvôli tomu aj tá premenná na začiatku), pretože naše vykresľovacie okno má výšku 100 bodov. A musíme zobrať do úvahy, že súradnicový systém SVG ide zhora nadol, takže y-ová súradnica 0 je vyššie na obrazovke ako súradnica 100. Každú čiaru vykreslíme so šírkou 6 bodov (vlastnosť “stroke-width”), modrou farbou (vlastnosť “stroke” – môže tam ísť aj HTML hexa kód farby, ako napr. “#0000FF”), a každá čiara bude od seba vzdialená 10 horizontálnych bodov. Na základe toho vyplníme vlastnosti “x1”, “y1” (tzn. na ktorých súradniciach má začínať naša čiara), a vlastnosti “x2” a “y2” (tzn. na ktorých súradniciach má končiť naša čiara).

Zoznam čiar/budúcich stĺpcov zlepíme potom dokopy do jedného reťazca funkciou CONCATENATEX jazyka DAX, a čiary usporiadame v tej istej funkcii v takom poradí, ako nasledujú mesiace v danom roku.

Text v merítku uzavrieme na konci koncovým tagom “</svg>”.

Po odklepnutí vzorca ešte nezabudnite nastaviť tomuto merítku vlastnosť “Kategória údajov” na možnosť “URL adresa obrázka” (návod nájdete v tomto článku alebo v mojej knihe o Power BI), a po dosadení merítka do tabuľky/kontingenčky to bude vyzerať takto:

Vidíte, že to pekne zobrazí modrý mini graf v tabuľke pre každý rok, kde sú obraty za každý mesiac usporiadané pekne pod sebou. Takisto vidíte, že to funguje aj v celkovom súčte na konci tabuľky, kde to v tomto prípade síce nemá moc zmysel. Ale v iných prípadoch – keď tam chcete niečo mať vykreslené a klasické podmienené formátovanie nefunguje – tak si tam môžete dať, čo chcete.

Ak tam nič nechcete mať, tak pridajte na začiatok vzorca jednoduchý test:

Graf =
VAR maximalnyObrat =
    MAXX ( VALUES ( 'Čas'[MonthNumberOfYear] ); [Obrat] )
RETURN
    "data:image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>"
    & 
    IF(HASONEFILTER('Čas'[CalendarYear] );
        CONCATENATEX (
            VALUES ( 'Čas'[MonthNumberOfYear] );
            "<line x1='"
                & (
                    CALCULATE ( MAX ( 'Čas'[MonthNumberOfYear] ) * 8 + 2 )
                ) & "' y1='" & ( 100 - ( [Obrat] * 100 / maximalnyObrat ) ) & "' x2='"
                & (
                    CALCULATE ( MAX ( 'Čas'[MonthNumberOfYear] ) ) * 8 + 2
                ) & "' y2='100' stroke='blue' stroke-width='6' />";
            "";
            'Čas'[MonthNumberOfYear]; ASC
        ) 
    ) & "</svg>"

Graf z celkového súčtu po tomto zmizne:

Vysvetlenie funkcie HASONEFILTER nájdete napr. v tomto článku.

Skladaný graf v tabuľke a podmienené vyfarbovanie

A čo teraz, keby sme chceli niečo pokročilejšie, napr. skladaný graf v tabuľke s podmieneným vyfarbovaním? Napríklad, chceme cez tento graf zobraziť čiaru s priemerným mesačným obratom, a tie stĺpce grafu, ktoré ho presahujú, zobraziť zelenou farbou?

V tomto prípade stačí len mierne upraviť tento vzorec. Najprv si v ňom vypočítať priemerný mesačný obrat, a potom do SVG bloku pridať ďalšiu čiaru, plus do vykresľovania pôvodných stĺpcov pridať dynamicky počítanú farbu.

Výsledný vzorec bude takýto:

Graf =
VAR maximalnyObrat =
    MAXX ( VALUES ( 'Čas'[MonthNumberOfYear] ); [Obrat] )
VAR priemernyObrat =
    AVERAGEX(VALUES('Čas'[MonthNumberOfYear]); [Obrat])
VAR priemernyObratY = 
    (100-(priemernyObrat * 100 / maximalnyObrat))
RETURN
    "data:image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>"
    & 
    IF(HASONEFILTER('Čas'[CalendarYear] );
        CONCATENATEX (
            VALUES ( 'Čas'[MonthNumberOfYear] );
            "<line x1='"
                & (
                    CALCULATE ( MAX ( 'Čas'[MonthNumberOfYear] ) * 8 + 2 )
                ) & "' y1='" & ( 100 - ( [Obrat] * 100 / maximalnyObrat ) ) & "' x2='"
                & (
                    CALCULATE ( MAX ( 'Čas'[MonthNumberOfYear] ) ) * 8 + 2
                ) & "' y2='100' stroke='" & IF([Obrat] > priemernyObrat; "#00FF00"; "blue") & "' stroke-width='6' />";
            "";
            'Čas'[MonthNumberOfYear]; ASC
        ) 
        & "<line x1='0' y1='" & priemernyObratY & "' x2='100' y2='" & priemernyObratY & "' stroke='black' stroke-width='1' />"
    )
    & "</svg>"

Vo vzorci sme si najprv vypočítali priemerný mesačný obrat do premennej priemernyObrat, a do ďalšej premennej sme si znormalizovali tú hodnotu na škálu 0 až 100, podobne ako sme to robili aj s tými stĺpcami v grafe. Na konci SVG dát sme potom pridali nový element ” <line>”, kde sme jednoducho vykreslili tenkú čiernu čiaru vo výške znormalizovaného priemerného obratu. A v poslednom kroku sme zmenili vykresľovanie pôvodných stĺpcov tak, že vlastnosť “stroke” vyplňujeme farbou “blue” alebo “#00FF00” (zelená), podľa toho, či obrat je alebo nie je väčší ako priemerný obrat. Po odklepnutí vzorca bude výsledok takýto:

A máme presne to, čo sme chceli 🙂 A možno aj kvôli tomuto nie je, a asi ani dlho nebude v Power BI zabudovaná podpora pre vkladanie grafov do tabuliek. Lebo sa to dá za pár minút spraviť pomocou SVG.

Každopádne, keď viete o všetkých možnostiach Power BI, tak aj takýto, na prvý pohľad komplikovaný a nepodporovaný prípad, ako je vložený graf v tabuľke, ide spraviť pomerne jednoducho. A v tomto prípade stačilo použiť iba jednoduché kombo jazykov SVG a DAX.

Ešte stále rozmýšľate o pokročilom kurze Power BI? 🙂

Michal Chmelár

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