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

Amikor első alkalommal ismerkedik meg a Power Pivot használatával, a legtöbb felhasználó felfedezi, hogy az eredmények összesítésében vagy kiszámításában van valódi ereje. Ha az adatok egy számértékeket tartalmazó oszlopot tartalmaznak, egyszerűen összesítheti őket, ha kijelöli őket egy kimutatásban vagy Power View mezőlistában. Mivel ez szám, a program automatikusan összege, átlagolása, megszámlálása vagy a kiválasztott összegzési típus szerint történik. Ezt implicit mértéknek nevezik. Az implicit measures nagyszerű a gyors és egyszerű összesítéshez, de vannak korlátaik, és ezeket a korlátokat szinte mindig felül lehet oldani az explicit intézkedések és számított oszlopok segítségével.

Először nézzük meg azt a példát, amikor egy számított oszlopban új szöveges értéket adunk hozzá egy Termék nevű táblázat minden egyes sorához. A Termék tábla minden egyes sorában mindenféle információ található az egyes értékesíteni kívánt termékekről. A termék neve, színe, mérete, ára stb. oszlopai vannak. Van egy másik, Product Category (Termékkategória) nevű kapcsolódó tábla, amely tartalmaz egy ProductCategoryName oszlopot. 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 Termék táblában létrehozhatunk egy Termékkategória nevű számított oszlopot a következőképpen:

Az új Termékkategória képlet a RELATED DAX függvény segítségével beszerz egy értéket a kapcsolódó Termékkategória táblázat ProductCategoryName oszlopában, majd beírja ezeket az értékeket a Termék táblázat minden egyes sorában.

Ez egy nagyszerű példa arra, hogy egy számított oszlop segítségével hogyan adhatunk hozzá rögzített értéket minden egyes sorhoz, amit később a kimutatás SOROK, OSZLOPOK vagy SZŰRŐK területén vagy egy kimutatásban Power View használhat.

Hozzunk létre egy másik példát, ahol ki szeretnénk számítani a termékkategóriák haszonkulcsát. Ez gyakori eset még számos oktatóanyag esetén is. Az adatmodellben található egy Értékesítés tábla, amely tranzakciós adatokat tartalmaz, és kapcsolatban áll az Értékesítés és a Termékkategória tábla között. Az Értékesítés táblában található egy oszlop, amely értékesítési mennyiségeket, egy másik pedig költségeket tartalmaz.

Létrehozhatunk egy számított oszlopot, amely kiszámolja az egyes sorok haszonkulcsát úgy, hogy kivonja a COGS oszlop értékeit a SalesAmount oszlop értékeiből, az alábbiak szerint:

Most létrehozhatunk egy kimutatást, és a Termékkategória mezőt az OSZLOPOK oszlopba húzjuk, az új Nyereség mezőt pedig az ÉRTÉKEK területre (a PowerPivot táblázatának egyik oszlopa a kimutatás mezőlistán található mező). Az eredmény egy implicit mérték, amely a Nyereség összege nevet kapta. A nyereségoszlop értékeinek összesített mennyisége az egyes különböző termékkategóriákban. Az eredmény így néz ki:

Ebben az esetben a Profit érték csak mezőként érthető a VALUES mezőként. Ha a Nyereség oszlopot az OSZLOPOK területre tennénk, kimutatásunk így nézne ki:

Profit mezőnk nem ad hasznos információt az OSZLOPOK, a SOROK vagy a SZŰRŐK területre helyezésekor. A függvény csak összesített értékként érthető az ÉRTÉKEK területen.

Létrehoztunk egy Profit nevű oszlopot, amely az Értékesítés tábla minden egyes sorához kiszámítja a haszonkulcsot. Ezután hozzáadtunk nyereséget a kimutatás ÉRTÉKEK területhez, automatikusan létrehozva egy implicit mértéket, ahol a rendszer az egyes termékkategóriákhoz kiszámítja az eredményt. Ha úgy gondolja, hogy valójában kétszer számítottunk nyereséget a termékkategóriákra, akkor helyesek. Először kiszámoltunk egy nyereséget az Értékesítés tábla minden egyes sorában, majd hozzáadtunk nyereséget az ÉRTÉKEK területhez, ahol az összesítve lett az egyes termékkategóriákhoz. Ha azt is gondolja, hogy nem igazán van szükség a Nyereség számított oszlop létrehozására, akkor helyesek is. De hogyan számíthatjuk ki a nyereséget anélkül, hogy létrehoztunk volna egy Profit számított oszlopot?

A nyereséget inkább explicit mértékként kellene kiszámítani.

Jelenleg a Nyereség számított oszlopot az Értékesítés táblázatban, a Termékkategória oszlopot pedig az OSZLOPOK, a nyereséget pedig a kimutatás ÉRTÉKEK oszlopában hagyjuk az eredmények összehasonlításához.

Az Értékesítés táblázat számítási területén létrehozunk egy Total Profit(összes nyereség) nevű mértéket (az elnevezési ütközések elkerülése érdekében). Végül ugyanazt az eredményt fogja eredményezni, mint korábban, de a Profit számított oszlop nélkül.

Először az Értékesítés táblában kijelöljük a SalesAmount oszlopot, majd az AutoSzum elemre kattintva létrehozunk egy explicit Értékesítésösszeg mértéket. Ne feledje, hogy az explicit mértékeket a Power Pivot-táblázat számítási területén hozjuk létre. A COGS oszlopban ugyanezeket a képleteket tudjuk megtenni. Átnevezjük ezeket a Total SalesAmount és a Total COGS nevet, hogy könnyebben azonosíthatók legyen.

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

Teljes nyereség:=[ Total SalesAmount] - [Total COGS]

Miután átváltottunk az új Teljes nyereség mérték formátumára pénznemre, felvehetjük azt a kimutatásba.

Az új Teljes nyereség mértéke ugyanazt az eredményt adja vissza, mint ha létrehoz egy Nyereség számított oszlopot, majd elhelyezi a VALUES mezőben. A különbség az, hogy a teljes nyereség mértéke sokkal hatékonyabb, és az adatmodellt tisztábbá és megbízhatóbbá teszi, mivel a számításokat az adott időpontban és csak a kimutatáshoz kiválasztott mezőkre használjuk. Végül is nincs igazán szükségünk a Nyereség számított oszlopra.

Miért fontos ez az utolsó rész? A számított oszlopok adatokat adnak az adatmodellhez, az adatok pedig memóriát vesznek fel. Ha frissítjük az adatmodellt, az erőforrások feldolgozására is szükség van a Profit oszlopban lévő összes érték újraszámításához. Nem igazán van szükség ehhez hasonló erőforrásokra, mert valójában a nyereséget szeretnénk kiszámítani, amikor kiválasztjuk a kimutatásban azokat a mezőket, amelyekhez nyereséget szeretnénk kihozni, például termékkategóriákat, régiót vagy dátum szerint.

Nézzünk egy másik példát. Az egyik, ahol egy számított oszlop olyan eredményt hoz létre, amely első pillantásra helyesnek tűnik, de...

Ebben a példában az értékesítési összegeket a teljes értékesítés százalékos arányaként szeretnénk kiszámítani. Az Értékesítés táblában létrehozunk egy Értékesítések %-a nevű számított oszlopot, a következőképpen:

A képlet a következőt tartalmazza: Az Értékesítés táblázat minden egyes sorában ossza el az Értékesítés oszlopban lévő mennyiséget a SUM total of all amount in the SalesAmount (Értékesítés)oszlop összegének összegével.

Ha létrehozunk egy kimutatást, és hozzáadjuk a Termékkategória oszlopot az OSZLOPOKhoz, és az Értékesítés %-a oszlopot az ÉRTÉKEK oszlopba adjuk, minden egyes termékkategória értékesítési %-ának összegét adjuk vissza.

oké. Ez eddig jól néz ki. De adjuk hozzá a szeletelőt. Hozzáadjuk a naptári évet, majd kiválasztunk egy évet. Ebben az esetben a 2007-es év van kiválasztva. Ezt tudjuk szerezni.

Első pillantásra ez még mindig helyesnek tűnhet. A százalékértékek azonban valójában 100%-ot kellene, hogy legyen, mivel szeretnénk tudni, hogy a 2007-es termékkategóriák minden egyes termékkategóriája hány százalékát szeretné tudni a teljes értékesítésnek. Mi történt?

A Forgalom %-a oszlop minden egyes sorhoz kiszámított egy százalékos értéket, amely a SalesAmount oszlop értékét elosztja a SalesAmount oszlop értékeinek összegével elosztva. A számított oszlopok értékeit kijavítottuk. A táblázat minden egyes sorához nem módosítható eredményt ad. Amikor hozzáadta az értékesítések %-át a kimutatáshoz, a kimutatás összegző értékként összegezve lett az Értékesítés oszlopban. Az Értékesítés %-a oszlop értékeinek összege mindig 100%.

Töröljük az Értékesítés %-a számított oszlopot, mert az nem segít nekünk. Ehelyett létrehozunk egy mértéket, amely megfelelően kiszámítja a teljes értékesítési százalékot, függetlenül attól, hogy szűrőket vagy szeletelőket alkalmazott-e.

Emlékszik a korábban létrehozott TotalSalesAmount mértékre, arra, amely egyszerűen összegzi a SalesAmount oszlopot? A Teljes nyereség mértékben ezt argumentumként használtjuk, és újra használni fogjuk argumentumként az új számított mezőben.

A következő képlettel hozunk létre egy új mértéket:

% of Total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

A képlet a következőt állítja be: A Total SalesAmount (Összes eladás) eredményének elosztása a SalesAmount összegével a kimutatásban definiáltaktól különböző oszlop- vagy sorszűrők nélkül.

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

Így jobban néz ki. Mostantól az egyes termékkategóriák összes eladásának %-a a 2007-es év összes eladásának százalékos arányaként van kiszámítva. Ha a CalendarYear szeletelőben másik vagy egy évnél több évet választunk, új százalékértékeket kapunk a termékkategóriákhoz, de a végösszeg továbbra is 100%. További szeletelőket és szűrőket is hozzáadhatunk. A Teljes értékesítés %-a mérték mindig a teljes értékesítés százalékos arányát fogja előállítani, függetlenül attól, hogy szeletelőket vagy szűrőket alkalmazott-e. A méri alapján az eredményt mindig az OSZLOPOK és a SOROK mezői, illetve az alkalmazott szűrők vagy szeletelők határozzák meg, hogy az eredményt a program mindig a környezet alapján számítja ki. Ez a intézkedések ereje.

Az alábbi útmutatások segítenek annak eldöntésében, hogy egy számított oszlop vagy mérték megfelelő-e egy adott számítási hez:

Számított oszlopok használata

• Ha azt szeretné, hogy az új adatok a kimutatás SORai, OSZLOPai vagy SZŰRŐi között, illetve egy TENGELY, JELMAGYARÁZAT vagy MOZAIK.SZERINT nézetben jelenjenek meg egy Power View megjelenítésben, számított oszlopot kell használnia. A hagyományos adatoszlophoz hasonló módon a számított oszlopok is használhatók mezőként bármely területen, szám esetén pedig az ÉRTÉKEK függvényben is összegződhet.

• Ha azt szeretné, hogy az új adatok fix értéket tartalmaznak a sorhoz. Tegyük fel például, hogy van egy dátumokat tartalmazó oszlopot tartalmazó dátumtáblája, és szeretne egy másik oszlopot is, 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 egyes sorhoz szöveges értéket szeretne hozzáadni egy táblázathoz, használjon számított oszlopot. A szöveges értékeket nem lehet összesíteni a VALUES függvényben. Az =FORMAT('Date'[Date];"mmmm") képlet például megadja a Dátum táblázat Dátum oszlopában lévő dátumok hónapnevét.

Amékek használata

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

• Ha összetettebb számításokat kell végeznie, például számításokat kell végeznie egy rendezési szűrő alapján, vagy éves eltérést szeretne számítani, használjon számított mezőt.

• Ha minimálisra szeretné csökkenteni a munkafüzet méretét, és maximalizálni szeretné a teljesítményét, a lehető legtöbb számítást hozza létre. Sok esetben az összes számítás mérete lehet, ami jelentősen csökkenti a munkafüzet méretét, és gyorsíthatja a frissítési időt.

Ne feledje, hogy semmi gond a számított oszlopok létrehozásával, mint a Profit oszlopunkban, majd egy kimutatásban vagy kimutatásban aggregálásával. Valójában ez egy nagyon jó és egyszerű módszer a saját számítások megismerére és létrehozására. Ahogy egyre jobban megérti a Power Pivot e két rendkívül hatékony funkcióját, a lehető leghatékonyabb és legpontosabb adatmodellt szeretné létrehozni. Reméljük, hogy az itt tanultak segítenek. Van néhány egyéb igazán nagyszerű forrás is, amely segíthet. Íme néhány: A DAX-képletekkörnyezete, a Power Pivotés a DAX Erőforrásközpont összesítései. És bár ez egy kicsit speciálisabb, és a könyvelési és pénzügyi szakemberek felé van irányítva, az Excel Microsoft Power Pivot segítségével az eredmény-adatmodellezés és -elemzés a Microsoft Power Pivot segítségével nagyszerű adatmodellezési és képlet-példákkal töltődik be.