Power BI trükkök 1: Egyéni táblaszerkezetek létrehozása (Custom table / matrix)

Az elmúlt évek projektjei során sok olyan helyzetet kellett megoldanunk, ahol már nem volt elég a “sztenderd” Power BI lehetőségek használata, hanem valamilyen trükkösebb megoldáshoz kellett folyamodni. Ezek többnyire olyan feladatok voltak, amelyek során valamilyen mértékben a Power BI alapvető logikájának a mélyebb ismeretére, és annak úgymond kreatív felhasználására volt szükség, ahol a végső megoldás utólag már egyszerűnek tűnt, de rájönni, kitalálni már közel sem volt olyan egyértelmű. Ezért arra gondoltunk, hogy talán érdemes, és mások számára is érdekes lenne egy olyan blogposzt-sorozatot indítanunk, ahol ezekből a megoldásokból válogatunk. Ezt szeretnénk rendszeressé tenni, és két-három hetente megosztani veletek egy-egy ilyen megoldást, hátha másoknak is hasznosak lesznek.

A mai első posztban rögtön egy olyan trükköt mutatnánk be, ami rendszeresen felmerül az ügyfelek oldaláról, mint igény: Excel táblázatokat ültessünk át egy az egyben a Power BI-ba! Jogosan merülhet fel a kérdés, hogy mi ebben a nehéz, hiszen van táblázat, sőt még mátrix vizualizáció is…? Ez igaz, viszont átalában ott kezdődnek a problémák, amikor a meglévő Excel táblázat egy-egy oszlopában nem azonos adatok vannak (pl.: szám és százalék, netán üres sorok tagolásként). Hogy jobban érthetővé válljon a feladat, íme egy egyszerű példa rá:

A nagyon nagy baj ezzel a helyzettel, hogy ez alapvetően nem lehetséges a Power BI-ban, hiszen szembe megy azzal a logikával, hogy aggregált adatokat osszunk fel egy-egy dimenzió mentén, mégis szinte minden ügyfélnél készítenek ehhez hasonló megoldásokat Excelben, amihez sok esetben ragaszkodnának a Power BI-ban is. Ilyenkor nincs mit tenni, meg kell oldanunk valahogy, és természetesen meg is fogjuk!

De lássuk mit tudunk tenni? Ilyen esetekben a Power BI táblázatoknak/mátrixoknak azt a tulajdonságát tudjuk kihasználni, hogy minden sor (és a mátrixoknál az oszlop szintén) szűrőként hat a táblázatunk értékmezőjében lévő értékre. Tehát, a célunk az lenne, hogy sorszinten mondjuk meg, hogy melyik sorban milyen kalkulációra, és formára van szükségünk!

Ehhez először létre kell hoznunk a sorok fejléceit és azok azonosítóit egy külön forrás táblában a Power Query editorban. Ezt legkönnyebben az Enter Data menüpont segítségével tehetjük meg.

PQ table

Ahogyan a neve is mutatja, ezt fogjuk használni sorfejlécként. A sorszámozásra pedig azért van szükség, hogy ne csak elnevezés alapján tudjuk majd sorba rendezni az egyes sorokat. Miután ezt mezőt betettük egy matrix vizualizáció sor mezőjébe, és sorba is rendeztük az index oszlop számai alapján, valahogy így fog kinézni a dolog:

Most, hogy a fejléc már meg is van, nincs más dolgunk, mint értékekkel feltölteni. És itt jön a trükk! Mert kihasználhatjuk a már említett tulajdonságát a Power BI táblázatoknak, hogy egy-egy sor fejléce szűrőként hat majd a használt értékre! Tehát létre kell hoznunk egy olyan measure-t, ami azt mondja meg, hogy ha a sor fejlécében az érték 1 (ez az indexe az amerikai bevételeknek), akkor a számításunk legyen az hogy…És így tovább minden sorra.

Tudom ez nagy munkának tűnik (és az is…), de ha ez a kérés, akkor ennek kell valahogy eleget tenni!:)

Miután teljesen megírtuk a measure-t, valahogy így fog kinézni:

Values = VAR RFE =
    CALCULATE ( [Total Revenue], Location[Continent] = "Europe" )
VAR RFA =
    CALCULATE ( [Total Revenue], Location[Continent] = "America" )
var TR=[Total Revenue]
var RFEP=RFE/TR
var RFAP=RFA/TR
return
SWITCH(SELECTEDVALUE(MatrixHeaders[Index]),
1,FORMAT(RFA,"#,##"),
2,FORMAT(RFE,"#,##"),
3,FORMAT(tr,"#,##"),
4,"-",
5,FORMAT(RFAP,"#0.00%"),
6,FORMAT(RFEP,"#0.00%"))

Itt a FORMAT függvényt azért használjuk, hogy ami százalék az úgy is nézzen ki, illetve a számok se “ömlesztve” jelenjenek meg.

Miután ezt a measure-t betettük az érték mezőbe, és az oszlopot tartalmát jobbra igazítottuk, illetve az vizualizáció oszlop mezőjébe betettük az év dimenzió értékeit, akkor végeredményként már meg is kapjuk a kért táblázatot. Ezt még formázzuk, hogy minél jobban hasonlítson az eredeti Excel táblázathoz, és a végeredmény valahogy így fog kinézni:

PBI table

Ahogy utólag már látható, nem egy bonyolult megoldásról van szó, hiszen nincsenek benne összetettebb kalkulációk, nem kell adatmodell-t módosítani, de annyira mégsem egyértelmű egy-egy ilyen megoldást kitalálni a nulláról…

Mindenesetre remélem, ezzel tudtunk most segíteni, és hasznos lesz számotokra is. Májusban folytatjuk!