Mikor célszerű számított oszlopokat és számított mezőket használni?

A Power Pivot használatának első elsajátításakor a felhasználók többsége rájön, hogy az igazi teljesítmény valamilyen módon az eredmények összesítése vagy kiszámítása során van. Ha az adatok numerikus értékeket tartalmazó oszlopot tartalmaznak, egyszerűen összesítheti azokat, ha kijelöli azokat egy kimutatásban vagy a Power View mezőlistájában. Természete szerint, mivel numerikus, a rendszer automatikusan összeadja, átlaga, számlálja vagy bármilyen típusú összesítést választ. Ezt implicit mértéknek nevezzük. Az implicit mértékek nagyszerűek a gyors és egyszerű összesítéshez, de vannak korlátaik, és ezek a korlátok szinte mindig leküzdhetők explicit mértékekkel és számított oszlopokkal.

Lássunk egy példát, amelyben egy számított oszlop használatával adtunk hozzá egy új szöveges értéket egy Product nevű tábla minden sorához. A Product (Termék) tábla minden sora mindenféle információt tartalmaz az egyes értékesített termékekről. A Terméknév, a Szín, a Méret, a Kereskedői ár stb. oszlopokkal rendelkezünk. Van egy másik kapcsolódó, Product Category nevű táblánk, amely a ProductCategoryName oszlopot tartalmazza. Azt szeretnénk, hogy a Termék tábla minden terméke tartalmazza a termékkategória nevét a Termékkategória táblából. A Product (Termék) táblában létrehozhatunk egy Product Category (Termékkategória) nevű számított oszlopot a következő módon:

Az új Termékkategória képlet a RELATED DAX függvénnyel lekér értékeket a kapcsolódó Termékkategória tábla ProductCategoryName oszlopából, majd megadja ezeket az értékeket minden termékhez (minden sorhoz) a Product táblában.

Ez egy nagyszerű példa arra, hogyan adhatunk hozzá egy rögzített értéket a számított oszlopokkal minden egyes sorhoz, amelyet később felhasználhatunk a kimutatás SOROK, OSZLOPOK vagy SZŰRŐK területén vagy egy Power View-jelentésben.

Hozzunk létre egy másik példát, amelyben a termékkategóriákhoz tartozó haszonkulcsot szeretnénk kiszámítani. Ez egy gyakori forgatókönyv, még sok oktatóanyagban is. Az adatmodellben van egy Sales tábla, amely tranzakciós adatokat tartalmaz, és van kapcsolat a Sales tábla és a Product Category tábla között. A Sales (Értékesítések) táblában van egy oszlop, amely értékesítési összegekkel és egy másik, költségekkel rendelkező oszloppal rendelkezik.

Létrehozhatunk egy számított oszlopot, amely az egyes sorok nyereségösszegét számítja ki úgy, hogy kivonja a COGS oszlop értékeit a SalesAmount oszlop értékeiből, az alábbi módon:

Most létrehozhatunk egy kimutatást, és áthúzhatjuk a Termékkategória mezőt az OSZLOPOK, az új Profit mezőt pedig az ÉRTÉKEK területre (a PowerPivot egyik táblázatának egyik oszlopa a Kimutatás mezőlistájának mezője). Az eredmény egy Sum of Profit nevű implicit mérték. Ez a különböző termékkategóriák profit oszlopában szereplő értékek összesített összege. Az eredmény a következőképpen néz ki:

Ebben az esetben a Nyereség csak az ÉRTÉKEK mezőjeként értelmezhető. Ha a Nyereséget az OSZLOPOK területre helyeznénk, a kimutatás a következőképpen nézne ki:

A Profit mező nem nyújt hasznos információt, ha OSZLOPOK, SOROK vagy SZŰRŐK területen van elhelyezve. Ez csak összesített értékként értelmezhető az ÉRTÉKEK területen.

Létrehozunk egy Profit nevű oszlopot, amely kiszámítja a Sales tábla egyes soraihoz tartozó haszonkulcsot. Ezután hozzáadtuk a Nyereséget a kimutatás ÉRTÉKEK területéhez, automatikusan létrehozva egy implicit mértéket, amelyben az eredmény minden termékkategóriára ki lesz számítva. Ha úgy gondolja, hogy a termékkategóriák nyereségét kétszer számítottuk ki, akkor helyes. Először kiszámítottunk egy nyereséget a Sales tábla minden sorához, majd hozzáadtuk a Profit értéket az ÉRTÉKEK területhez, ahol az összesítve lett az egyes termékkategóriákhoz. Ha azt is gondolja, hogy nem igazán kellett létrehozni a Profit számított oszlopot, akkor az is helyes. De hogyan számítjuk ki a nyereséget anélkül, hogy létrehoznánk a Profit számított oszlopot?

A nyereséget valóban jobb lenne explicit mértékként kiszámítani.

Egyelőre a Sales (Értékesítések) táblában a Profit (Nyereség) számított oszlopban, a Product Category (Termékkategória) oszlopban pedig az COLUMNS (OSZLOPOK), a Profit (Nyereség) oszlopban pedig a kimutatás értékeit fogjuk használni az eredmények összehasonlításához.

A Sales tábla számítási területén létrehozunk egy Total Profit nevű mértéket (az elnevezési ütközések elkerülése érdekében). Végül ugyanazt az eredményt adja, mint korábban, de profit számított oszlop nélkül.

Először a Sales táblában kiválasztjuk a SalesAmount oszlopot, majd az AutoSzum gombra kattintva létrehozunk egy explicit Sum of SalesAmountmértéket. Ne feledje, hogy egy explicit mértéket a Power Pivotban egy tábla számítási területén hozunk létre. Ugyanezt tesszük a COGS oszlop esetében is. Átnevezzük ezeket a Total SalesAmount és a Total COGS nevet, hogy könnyebben azonosíthatók legyenek.

Ezután létrehozunk egy másik mértéket a következő képlettel:

Teljes nyereség:=[ Összes értékesítésösszeg] – [Összes COGS]

Miután az új Total Profit mérték formátumát pénznemre módosította, hozzáadhatjuk a kimutatáshoz.

Láthatja, hogy az új Total Profit mérték ugyanazokat az eredményeket adja vissza, mint egy Profit számított oszlop létrehozása, majd az ÉRTÉKEK értékben való elhelyezése. A különbség az, hogy a Total Profit mérték sokkal hatékonyabb, és tisztábbá és letisztultabbá teszi az adatmodellt, mivel akkor számítunk, és csak a kimutatáshoz kiválasztott mezőkre. Végül is nincs szükségünk a Profit számított oszlopra.

Miért fontos ez az utolsó rész? A számított oszlopok adatokat adnak hozzá az adatmodellhez, és az adatok memóriát foglalnak el. Az adatmodell frissítésekor a feldolgozási erőforrásokra is szükség van a Profit oszlopban szereplő összes érték újraszámításához. Nem kell ehhez hasonló erőforrásokat használnunk, mert valóban ki szeretnénk számítani a nyereségünket, amikor kiválasztjuk a kimutatásban azokat a mezőket, amelyekhez profitot szeretnénk adni, például termékkategóriák, régió vagy dátumok szerint.

Lássunk egy másik példát. Az egyik, ahol egy számított oszlop olyan eredményeket hoz létre, amelyek első pillantásra helyesnek tűnnek, de......

Ebben a példában az értékesítési összegeket a teljes értékesítés százalékában szeretnénk kiszámítani. A Sales táblában létrehozunk egy % of Sales nevű számított oszlopot a következőképpen:

A képlet állapota: A Sales tábla minden sorában ossza el a SalesAmount oszlopban lévő összeget a SalesAmount oszlopban lévő összes összeg összegével.

Ha létrehozunk egy kimutatást, és hozzáadjuk a Product Category (Termékkategória) oszlopot az COLUMNS (OSZLOPOK) oszlophoz, és kiválasztjuk a Sales (Értékesítések %-a) oszlopot az VALUES (ÉRTÉKEK) oszlopba, akkor az egyes termékkategóriák értékesítéseinek százalékos összegét kapjuk meg.

Oké. Eddig jól néz ki. De adjunk hozzá egy szeletelőt. Hozzáadjuk a Naptári évet, majd kiválasztunk egy évet. Ebben az esetben a 2007-et választjuk. Ez az, amit kapunk.

Első pillantásra ez továbbra is helyesnek tűnhet. A százalékos értékünknek azonban valóban 100%-nak kell lennie, mert szeretnénk tudni, hogy 2007-ben az egyes termékkategóriákban hány százalékot értékesítettünk. Szóval mi volt a baj?

A Sales %of Sales oszlop kiszámított egy százalékot minden egyes sorhoz, amely a SalesAmount oszlopban lévő érték, osztva a SalesAmount oszlop összes értékének összegével. A számított oszlop értékei rögzítettek. A tábla minden sorához nem módosítható eredmény. Amikor hozzáadtuk az értékesítés %-át a kimutatáshoz, az összesítés a SalesAmount oszlop összes értékének összegeként történt. A Sales oszlop %. oszlopában szereplő összes érték összege mindig 100%.

Törölheti a Sales (Értékesítés%-) számított oszlopot, mert az nem segít. Ehelyett létrehozunk egy mértéket, amely megfelelően kiszámítja a teljes értékesítés százalékos arányát, függetlenül az alkalmazott szűrőktől vagy szeletelőktől.

Emlékszik a korábban létrehozott TotalSalesAmount mértékre, amely egyszerűen összegzi a SalesAmount oszlopot? A Total Profit mértékben argumentumként használtuk, és ismét argumentumként fogjuk használni az új számított mezőben.

Új mértéket hozunk létre a következő képlettel:

Az összes értékesítés %-a:=([Összes értékesítésösszeg]) / CALCULATE([Összes értékesítésösszeg], ALLSELECTED())

A képlet állapota: Ossza el a Total SalesAmount függvény eredményét a SalesAmount összegével a kimutatásban meghatározottaktól eltérő oszlop- vagy sorszűrők nélkül.

Ha hozzáadjuk az összes értékesítés új %-át a kimutatáshoz, a következőt kapjuk:

Ez jobban néz ki. Most az egyes termékkategóriák teljes értékesítésének százalékos aránya a 2007-es év teljes értékesítésének százalékában lesz kiszámítva. Ha egy másik évet vagy egy évnél többet választunk a CalendarYear szeletelőben, új százalékos értékeket kapunk a termékkategóriákhoz, de a végösszeg még mindig 100%. További szeletelőket és szűrőket is hozzáadhatunk. Az Összes értékesítés %-a mérték mindig az összes értékesítés százalékos arányát fogja eredményezni, függetlenül az alkalmazott szeletelőktől vagy szűrőktől. Mértékek esetén az eredmény kiszámítása mindig az OSZLOPOK és SOROK mezők, valamint az alkalmazott szűrők vagy szeletelők által meghatározott környezet alapján történik. Ez a mértékek ereje.

Az alábbiakban néhány útmutatást talál annak eldöntéséhez, hogy egy számított oszlop vagy mérték megfelel-e egy adott számítási igénynek:

Számított oszlopok használata

• Ha azt szeretné, hogy az új adatok megjelenjenek a sorokban, oszlopokban vagy szűrőkben egy kimutatásban, illetve egy TENGELY, JELMAGYARÁZAT vagy CSEMPE BY értéken egy Power View-vizualizációban, számított oszlopot kell használnia. A normál adatoszlopokhoz hasonlóan a számított oszlopok is használhatók mezőként bármely területen, és ha numerikusak, akkor értékekben is összesíthetők.

• Ha azt szeretné, hogy az új adatok rögzített értéket adjanak a sornak. Van például egy dátumoszlopot tartalmazó dátumtáblája, és egy másik oszlopot szeretne, amely csak a hónap számát tartalmazza. Létrehozhat egy számított oszlopot, amely csak a hónap számát számítja ki a Dátum oszlop dátumaiból. Például: =HÓNAP('Dátum'[Dátum]).

• Ha minden sorhoz szöveges értéket szeretne hozzáadni egy táblázathoz, használjon számított oszlopot. A szöveges értékeket tartalmazó mezők soha nem összesíthetők értékekben. Az =FORMAT('Date'[Date];"mmmm") például a Date tábla Date oszlopában szereplő dátumok hónapnevét adja meg.

Mértékek használata

• Ha a számítás eredménye mindig a kimutatásban kiválasztott többi mezőtől függ.

• Ha összetettebb számításokat kell végeznie, például egy számlálót valamilyen szűrő alapján kell kiszámítania, vagy egy éven túli évet vagy varianciát kell kiszámítania, használjon számított mezőt.

• Ha a munkafüzet méretét minimálisra szeretné csökkenteni, és maximalizálni szeretné a teljesítményét, hozzon létre annyi számítást, amennyit csak lehet. Sok esetben az összes számítás mértéke lehet, ami jelentősen csökkenti a munkafüzet méretét, és felgyorsítja a frissítési időt.

Ne feledje, hogy nincs semmi baj azzal, ha számított oszlopokat hozunk létre, mint a Profit oszlopban, majd összesítéssel egy kimutatásban vagy jelentésben. Ez valójában egy nagyon jó és egyszerű módja annak, hogy megismerje és létrehozza a saját számításait. A Power Pivot e két rendkívül hatékony funkciójának ismeretében érdemes a lehető leghatékonyabb és legpontosabb adatmodellt létrehozni. Remélhetőleg az, amit itt tanult, segít. Vannak más igazán nagyszerű források is, amelyek segíthetnek Önnek is. Íme néhány: Környezet a DAX-képletekben, a Power Pivot összesítései és a DAX-erőforrásközpont. És bár ez egy kicsit fejlettebb, és könyvelési és pénzügyi szakembereknek szól, a Profit and Loss Data Modeling and Analysis with Microsoft Power Pivot in Excel minta nagyszerű adatmodellezéssel és képletpéldánnyal rendelkezik.