A Power Pivot használatának első elsajátításakor a legtöbb felhasználó rájön, hogy az eredmények összegzése vagy kiszámítása rejlik az igazi hatékonyság. Ha az adatok tartalmaznak numerikus értékeket tartalmazó oszlopot, egyszerűen összesítheti azt egy kimutatásban vagy egy Power View nézetbeli mezőlistában való kijelöléssel. Mivel számérték, automatikusan összegzik, átlagolja, megszámolja vagy bármilyen típusú összesítést tartalmaz. Ezt implicit mértéknek nevezzük. Az implicit mértékek kiválóan alkalmasak a gyors és könnyű összesítésre, de korlátaik vannak, és ezek a korlátok szinte mindig leküzdhetők explicit mértékekkel és számított oszlopokkal.
Először tekintsünk egy példát, amelyben egy számított oszlopban új szöveges értéket adunk hozzá egy Termék nevű táblázat minden sorához. A Terméktáblázat minden sora mindenféle információt tartalmaz az általunk forgalmazott termékekről. Oszlopaink vannak a Terméknév, Szín, Méret, Kereskedő ára stb. számára. Van egy másik Termékkategória nevű kapcsolódó táblánk, amely tartalmaz egy ProductCategoryName nevű oszlopot. Azt szeretnénk, hogy a Product (Termék) táblázatban szereplő minden termék tartalmazza a termékkategória nevét a Termékkategória táblából. A Termékek táblázatban létrehozhatunk egy Termékkategória nevű számított oszlopot, így:
Az új Termékkategória képlet a RELATED DAX függvény segítségével lekéri az értékeket a kapcsolódó Termékkategória táblázat ProductCategoryName oszlopából, majd beírja ezeket az értékeket a Terméktáblázat egyes termékeihez (soronként).
Ez egy nagyszerű példa arra, hogy számított oszlopok használatával hogyan adhatók hozzá rögzített értékek minden sorhoz, amelyet később a kimutatások SOROK, OSZLOPOK vagy SZŰRŐK területén vagy egy Power View nézetet tartalmazó jelentésben használhatunk.
Hozzunk létre egy másik példát, amelyben a termékkategóriák haszonkulcsát szeretnénk kiszámítani. Ez gyakori forgatókönyv, még sok oktatóanyagban is. Az adatmodellünkben van egy tranzakciós adatokat tartalmazó Értékesítés táblázat, és kapcsolat van az Értékesítés és a Termékkategória tábla között. Az Értékesítés táblázatban van egy oszlop, amely az értékesítési összegeket tartalmazza, és egy másik oszlop, amely a költségeket tartalmazza.
Létrehozhatunk egy számított oszlopot, amely minden sorhoz kiszámítja a haszon összegét, ha kivonja a COGS oszlop értékeit az ÉrtékesítésMennyisége oszlop értékeiből, a következőképpen:
Most létrehozhatunk egy kimutatást, és a Termékkategória mezőt az OSZLOPOK területre húzzuk, az új Nyereség mezőt pedig az ÉRTÉKEK területre (a PowerPivot-táblázatok oszlopai a kimutatás mezőlistájában is szerepelnek). Az eredmény egy Sum of Profit nevű implicit mérték lesz. Ez a különböző termékkategóriák Nyereség oszlopában lévő értékek összesített összege. Az eredmény így néz ki:
Ebben az esetben a Nyereségnek csak a VALUES táblában van értelme. Ha a Profit oszlopokat helyezné az OSZLOPOK területre, a kimutatásunk a következőképpen nézne ki:
A Nyereség mező nem nyújt hasznos információt, ha az OSZLOPOK, a SOROK vagy a SZŰRŐK területre kerül. Ennek csak összesített értékként van értelme az ÉRTÉKEK területen.
Létrehoztunk egy Nyereség nevű oszlopot, amely kiszámítja a haszonkulcsot az Értékesítés táblázat minden sorához. Ezután hozzáadtuk a nyereség mezőt a kimutatás ÉRTÉKEK területéhez, automatikusan létrehozva egy implicit mértéket, ahol a rendszer minden egyes termékkategóriához kiszámít egy eredményt. Ha azt gondolja, hogy tényleg kétszer számoltunk nyereséget a termékkategóriákhoz, akkor igaza van. Először nyereséget számoltunk az Értékesítés táblázat minden sorára, majd hozzáadtuk a Profit mezőt az ÉRTÉKEK területhez, ahol azt összesítettük az egyes termékkategóriákhoz. Ha azt is gondolja, hogy valójában nem kellett létrehoznunk a Profit számított oszlopot, akkor igaza is van. De hogyan számítsuk ki a nyereséget anélkül, hogy létrehoznánk a Profit számított oszlopot?
A profitot valóban jobban ki kellene számolni explicit mérőszámként.
Az eredmények összehasonlításához egyelőre meghagyjuk a kimutatás számított nyereség oszlopát, az eredmény összehasonlításához pedig a kimutatás "Termékkategória" oszlopát az OSZLOPOK és a Nyereség az ÉRTÉKEK táblában.
Az Értékesítés táblázat Számítási területén létrehozunk egy Teljes nyereség nevű mértéket (az elnevezésütközések elkerülése érdekében). Végül ugyanazt az eredményt adja, mint korábban, de nincs Nyereség számított oszlop.
Először a Sales táblában jelöljük ki a SalesAmount oszlopot, majd az AutoSzum gombra kattintva létrehozzuk a SalesAmount (SalesAmount összeg) explicit mértéket. Ne feledje, hogy explicit mértéket a PowerPivot-táblázatok számítási területén hozunk létre. Ugyanezt tesszük a COGS oszloppal is. Ezeket átnevezzük Total SalesAmount és Total COGS névre, hogy könnyebb legyen azonosítani őket.
Ezután létrehozunk egy másik mértéket ezzel a képlettel:
Total Profit:=[Total SalesAmount] - [Total COGS]
Megjegyzés
A képletünket megírhatnánk a következőképpen is: Total Profit:=SUM([SalesAmount]) - SUM([COGS]), de külön Total SalesAmount és Total COGS mértékek létrehozásával a kimutatásban is felhasználhatjuk őket, illetve számos más mértékképletben is használhatjuk őket argumentumként.
Miután pénznemre módosítottuk a Teljes nyereség mérték formátumát, hozzáadhatjuk a kimutatáshoz.
Láthatja, hogy az új Teljes nyereség mérték ugyanazokat az eredményeket adja, mintha egy Profit számított oszlopot hozna létre, majd az ÉRTÉKEK oszlopba helyezné. A különbség az, hogy a Teljes nyereség mérték sokkal hatékonyabb, valamint tisztábbá és letisztultabbá teszi az adatmodellünket, mivel a számítást abban az időben végezzük, és csak a kimutatáshoz kiválasztott mezőkre végezzük. Végül is nincs igazán szükségünk arra a Profit 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 foglalnak. Az adatmodell frissítésekor feldolgozási erőforrásokra is szükség lesz a Nyereség oszlop összes értékének újraszámolásához. Nincs igazán szükségünk ilyen erőforrásokra, mert a nyereséget akkor szeretnénk kiszámítani, amikor kiválasztjuk a kimutatásban azokat a mezőket, amelyekre a nyereséget szeretnénk beállítani, például a termékkategóriák, a régió vagy a dátum szerint.
Nézzünk egy másik példát. Olyan, ahol egy számított oszlop első pillantásra helyesnek tűnő eredményeket hoz létre, de...
Ebben a példában az értékesítési összegeket a teljes értékesítés százalékos arányában szeretnénk kiszámítani. Létrehozunk egy % of Sales nevű számított oszlopot az Értékesítés táblázatban, az alábbihoz hasonlóan:
A képlet a következő: A Sales táblázat minden egyes sorában ossza el a SalesAmount oszlopban lévő mennyiséget a SalesAmount oszlopban lévő összes összeg SUM összegével.
Ha létrehozunk egy kimutatást, és hozzáadjuk a Termékkategória mezőt az OSZLOPOKHOZ, majd kiválasztjuk az új "Értékesítés %" oszlopot az ÉRTÉKEK részbe helyezéséhez, akkor minden termékkategóriánk esetében az Értékesítés %-ának összegét kapjuk.
Rendben. Ez eddig jól néz ki. De vegyünk fel egy szeletelőt. Hozzáadjuk a Calendar Year mezőt, majd kiválasztunk egy évet. Ebben az esetben a 2007-es számot választjuk. Ezt kapjuk.
Első pillantásra ez még helyesnek tűnhet. De a százalékos aránynak valójában ki kellene emelkednie a 100%-ra, mivel szeretnénk tudni, hogy minden egyes termékkategóriánk 2007-ben elért összes forgalma százalékos arányban szerepel. Szóval mi romlott el?
Az "Értékesítés százaléka" oszlopunk kiszámította az egyes sorok százalékos arányát, amely a SalesAmount oszlopban szereplő érték elosztva a SalesAmount oszlopban lévő összes érték összegével. A számított oszlopok értékei rögzítettek. A táblázat minden sorában nem módosítható eredmény. Amikor hozzáadtuk az "Értékesítés %-a" értéket a kimutatáshoz, azt a SalesAmount oszlopban szereplő összes érték összegeként összesítettük. Az "Értékesítés %" oszlopban szereplő értékek összege mindig 100% lesz.
Tipp:
Ne felejtse el elolvasni a kontextust a DAX-képletekben. Jól megérti a sor- és szűrőszintű környezetet, amit itt ismertetünk.
Törölhetjük az "Értékesítés százaléka" számított oszlopot, mert az nem segít nekünk. Ehelyett egy olyan mértéket fogunk létrehozni, amely helyesen számítja ki a teljes értékesítés százalékos arányát, függetlenül attól, hogy alkalmaztak-e szűrőket vagy szeletelőket.
Emlékszik már a korábban létrehozott TotalSalesAmount mértékre, amely egyszerűen összeadja a SalesAmount oszlopot? Felhasználtuk argumentumként a Teljes nyereség mértékben, és a jövőben ismét argumentumként fogjuk használni az új számított mezőben.
Tipp:
Az olyan explicit mértékek létrehozása, mint a Total SalesAmount és a Total COGS, nemcsak kimutatásokban vagy jelentésekben hasznosak, hanem argumentumként is használhatók más mértékekben, amikor az eredményt argumentumként szeretné használni. Ezáltal a képletek hatékonyabbak és könnyebben olvashatók lesznek. Ez jó adatmodellezési gyakorlat.
Létrehozunk egy új mértéket a következő képlettel:
Teljes értékesítés százaléka:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
A képlet a következőt jelenti: A Total SalesAmount oszlop eredményének elosztása a SalesAmount mező összegével, a kimutatásban meghatározottakon kívül más oszlop- vagy sorszűrők nélkül.
Tipp:
A DAX-referencia CALCULATE és ALLSELECTED függvényeiről mindenképpen olvassa el a CALCULATE és az ALLSELECTED függvényt.
Ha hozzáadjuk az összes eladás új %-át a kimutatáshoz, a következőket kapjuk:
Ez jobbnak tűnik. Most az összes eladás %-át az egyes termékkategóriák esetében a 2007-es év összes forgalmának százalékos arányában számítja ki a rendszer. Ha másik évet választunk, vagy egynél több évet a CalendarYear szeletelőben, új százalékos értékeket kapunk a termékkategóriákhoz, de a végösszeg továbbra is 100%. Hozzáadhatunk más szeletelőket és szűrőket is. A % of Total Sales (Összes eladás) mérték mindig a teljes forgalom adott százalékos arányát produkálja, függetlenül az alkalmazott szeletelőktől és szűrőktől. A mértékek esetében az eredmény kiszámítása mindig az OSZLOPOK és a SOROK tábla mezői, illetve az alkalmazott szűrők vagy szeletelők által meghatározott környezet szerint történik. Ez az intézkedések ereje.
Az alábbiakban néhány útmutatást talál, amelyek segítenek eldönteni, hogy egy számított oszlop vagy mérték megfelelő-e egy adott számítási igényhez:
Számított oszlopok használata
- Ha azt szeretné, hogy az új adatok megjelenjenek egy kimutatás SOROK, OSZLOPOK vagy SZŰRŐK oszlopában, illetve egy Power View nézetbeli megjelenítésben egy TENGELY, JELMAGYARÁZAT vagy MOZAIKOKRA BONTÁS ALAPJA oszlopban, számított oszlopot kell használnia. A hagyományos adatoszlopokhoz hasonlóan a számított oszlopok is bármely területen használhatók mezőként, és ha numerikusak, akkor a VALUES oszlopban is összegezhetők.
- Ha azt szeretné, hogy az új adat rögzített érték legyen a sorhoz. Tegyük fel például, hogy van egy dátumokat tartalmazó táblázata, és szeretne egy másik oszlopot, 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 oszlopban lévő dátumok alapján. Például: =HÓNAP('Dátum'[Dátum]).
- Ha egy táblázat minden sorához hozzá szeretne adni egy szöveges értéket, használjon számított oszlopot. A szöveges értékeket tartalmazó mezők soha nem összegezhetők a VALUES utasításban. 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.
Mértékek használata
- Ha a számítás eredménye mindig függ a kimutatásban kiválasztott többi mezőtől.
- Ha összetettebb számításokat kell végeznie, például számlálót kell kiszámítania valamilyen szűrő alapján, vagy ki kell számítania egy éves eltérést, 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ényt, akkor minél több számítást hozzon létre mértékként. Sok esetben az összes számítás mérték lehet, amely jelentősen csökkenti a munkafüzet méretét, és felgyorsítja a frissítési időt.
Ne feledje, nincs semmi baj azzal, ha számított oszlopokat hoz létre, mint ahogyan a Nyereség oszloppal tettük, majd egy kimutatásban vagy jelentésben összesíti őket. Valójában ez egy nagyon jó és egyszerű módja a saját számítások megismerésének és létrehozásának. Minél jobban megérti a Power Pivot e két rendkívül hatékony funkcióját, törekedni fog arra, hogy a lehető leghatékonyabb és legpontosabb adatmodellt hozza létre. Remélhetőleg az itt tanultak a segítségére lesznek. Vannak más igazán jó források is, amelyek szintén segíthetnek. Íme néhány példa: Környezet a DAX-képletekben, Összesítések a Power Pivotban és a DAX-erőforrásközpontban. Habár egy kicsit fejlettebb, könyvelői és pénzügyi szakembereknek szóló készült, az Eredmény- és veszteségadatok modellezése és elemzése az Excelbeli Microsoft Power Pivot használatával nagyszerű adatmodellezési és képletpéldákat tartalmaz.