Millal kasutada arvutuslikke veerge ja arvutuslikke välju?

Power Pivoti kasutamise esmaõppimisel avastab enamik kasutajaid, et tegelik võimsus on mingil viisil tulemite ligemalemine või arvutamine. Kui teie andmetel on arvväärtustega veerg, saate need hõlpsalt liita, valides need PivotTable-liigendtabelis või Power View' väljaloendis. Kuna see on arvuline, liidetakse see loomult automaatselt, liidetakse, loendatakse, loendatakse või mis tahes tüüpi liitmine, mille valite. Seda nimetatakse kaudseks mõõtuks. Kaudsed kogumid on suurepärased kiireks ja lihtsaks koondamiseks, kuid neil on piirid ning neid piiranguid saab peaaegu alati ületada otseste meetmetega jaarvutatud veergudega.

Vaatame esmalt näidet, kus kasutame arvutatud veergu, et lisada tabeli toote iga rea jaoks uus tekstiväärtus. Iga tabeli Toode rida sisaldab igat tüüpi teavet iga meie müüdud toote kohta. Meil on veerud tootenime, värvi, suuruse, edasimüüja hinna jne jaoks. Meil on veel üks seotud tabel nimega Tootekategooria, mis sisaldab veergu ProductCategoryName. Soovime, et iga tootetabeli toode kaasaks tootekategooria nime tabelist Tootekategooria. Tootetabelis saame luua arvutatud veeru nimega Tootekategooria järgmiselt.

Meie uus tootekategooria valem kasutab funktsiooni RELATED DAX, et saada väärtused seotud tootekategooria tabeli veerust ProductCategoryName ja seejärel sisestab need väärtused iga toote (iga rea) kohta tabelisse Toode.

See on suurepärane näide sellest, kuidas saame arvutusliku veeru abil lisada kindla väärtuse iga rea jaoks, mida saame kasutada hiljem PivotTable-liigendtabeli alas READ, VEERUD või FILTRID või Power View' aruandes.

Loome veel ühe näite, kus soovime arvutada oma tootekategooriate kasumimarginaali. See on levinud stsenaarium, isegi paljude õppevideote korral. Meie andmemudelis on tabel Müük, mis sisaldab tehinguandmeid ning tabeli Müük ja tabeli Tootekategooria vahel on seos. Tabelis Müük on veerg, kus on müügisummad ja teine veerg, mis sisaldab kulusid.

Saame luua arvutatud veeru, mis arvutab iga rea kasumisumma, lahutades veeru COGS väärtused veeru SalesAmount väärtustest järgmiselt.

Nüüd saame luua PivotTable-liigendtabeli ja lohistada välja Tootekategooria veergudesse ja meie uus kasum alale VÄÄRTUSED (tabeli veerg PowerPivot on PivotTable-liigendtabeli väljaloendi väli). Tulem on kaudne mõõt nimega Kasumi summa. See on iga tootekategooria kasumiveeru väärtuste liitsumma. Meie tulem näeb välja selline:

Sel juhul on väärtus Kasum mõistlik ainult väljal VÄÄRTUSED. Kui lisame kasumi alale VEERUD, näeks meie PivotTable-liigendtabel välja selline:

Väli Kasum ei anna kasulikku teavet, kui see paigutatakse alale VEERUD, READ või FILTRID. See on mõistlik ainult alas VALUE agregaadväärtusena.

Oleme loonud veeru nimega Kasum, mis arvutab tabeli Müük iga rea kasumimarginaali. Seejärel lisasime oma PivotTable-liigendtabeli alale VÄÄRTUSED väärtuse, luues automaatselt kaudse mõõtu, kus iga tootekategooria tulem arvutatakse. Kui mõtlete, et arvutasime oma tootekategooriate kasumit kaks korda, on teil õigus. Esmalt arvutasime tabeli Müük iga rea jaoks kasumi ja lisasime seejärel väärtuse alale VÄÄRTUSED, kus see iga tootekategooria kohta koondati. Kui mõtlete ka, et meil ei olnud tegelikult vaja arvutada veergu Kasum, on teil ka õigus. Kuidas aga arvutada oma kasum ilma arvutatud veeruta Kasum?

Kasum oleks tõepoolest parem arvutada kui otsest mõõtu.

Praegu jätame oma PivotTable-liigendtabeli tulemite võrdlemiseks oma arvutatud veerud Kasum tabelisse Müük ja Tootekategooria veergudesse ja Kasum väärtustesse.

Tabeli Müük arvutusalas loome mõõtu nimega Kogukasum (konfliktide nimetamise vältimiseks). Lõpuks annab see samad tulemid nagu varem, kuid ilma arvutatud veeruta Kasum.

Esmalt valime tabelis Müük veeru Müügisumma ja seejärel klõpsake nuppu Automaatsumma, et luua mõõt Müügisumma summa. Pidage meeles, et power Pivoti tabeli arvutusalas luuakse otsene mõõt. Teeme sama veeru COGS puhul. Nimetame need Total SalesAmount ja Total COGS ümber, et neid oleks lihtsam tuvastada.

Seejärel loome järgmise valemi abil veel ühe mõõtu.

Kasum kokku:=[ Müügisumma kokku] - [Omahinnad kokku]

Pärast uue summatulu mõõtu vormingu muutmist valuutaks saame selle pivottable-liigendtabelisse lisada.

Näete, et meie uus summakasumi mõõt tagastab samad tulemid nagu arvutatud veeru Kasum loomine ja seejärel väärtustesse paigutamine. Erinevus seisneb selles, et kogukasumi mõõt on palju tõhusam ning muudab meie andmemudeli puhtamaks ja säästlikumaks, kuna arvutame seda praegu ja ainult pivotTable-liigendtabeli jaoks valitud väljade jaoks. Tegelikult pole meil seda arvutatud veergu vaja.

Miks on see viimane osa oluline? Arvutatud veerud lisavad andmemudelisse andmeid ja andmed koguvad mälu. Kui värskendame andmemudelit, on vaja töödelda ressursse ka veeru Kasum kõigi väärtuste ümberarvutamiseks. Me ei pea tegelikult ressursse nii ära võtma, kuna soovime oma kasumit arvutada, kui valime PivotTable-liigendtabelis väljad, mille jaoks soovime kasumit (nt tootekategooriad, regioon või kuupäevad).

Vaatame veel ühte näidet. See, kus arvutuslik veerg loob tulemid, mis esmapilgul näevad välja õiged, kuid....

Selles näites soovime arvutada müügisummad protsendina kogumüügist. Loome tabelis Müük arvutuslik veerg nimega % müügist:

Meie valem on järgmine. Tabeli Müük iga rea puhul jagage veerus SalesAmount toodud summa veeru SalesAmount kõigi summade summa summaga.

Kui loome PivotTable-liigendtabeli ja lisame veerusse COLUMNS tootekategooria ja valime uue % müügiveergudest, et see väärtustesse lisada, saame iga tootekategooria kohta kogusumma % müügist.

Ok. Siiani näeb see hea välja. Kuid lisame tükeldi. Lisame kalendriaasta ja seejärel valime aasta. Sel juhul valime 2007. See on see, mis me saame.

Esmapilgul võib see siiski õige olla. Kuid meie protsendid peaksid tegelikult kokku olema 100%, kuna soovime teada protsentuaalset osa kõigi 2007. aasta tootekategooriate kogumüügist. Mis siis valesti läks?

Meie veerg Müük arvutas protsendi iga rea kohta, mis on veeru SalesAmount väärtus jagatuna veeru SalesAmount kõigi väärtuste summaga. Arvutatud veeru väärtused on fikseeritud. Need on tabeli iga rea muutumatu tulem. Kui lisasime oma PivotTable-liigendtabelisse protsendi müügist, koondati see veeru SalesAmount kõigi väärtuste summana. Veeru Müük % kõigi väärtuste summa on alati 100%.

Saame kustutada oma protsendi arvutatud veerust Müük, kuna see ei aita meid. Selle asemel loome mõõtu, mis arvutab õigesti meie protsendi kogumüügist, sõltumata rakendatud filtritest või tükelditest.

Kas mäletate mõõtu TotalSalesAmount, mille lõime varem, see, mis lihtsalt summeerib veeru SalesAmount? Kasutasime seda argumendina oma kogukasumi mõõtu ja kasutame seda uuesti argumendina uuel arvutusväljal.

Loome uue mõõtu järgmise valemiga.

% kogumüügist:=([Müügisumma kokku]) / CALCULATE([Müügisumma kokku], ALLSELECTED())

Selles valemis on esitatud järgmine: Jagage tulem summast Total SalesAmount summaga SalesAmount ilma pivottable-liigendtabelis määratlemata veeru- või reafiltriteta.

Nüüd, kui lisame PivotTable-liigendtabelisse uue % kogumüügist, saame teada:

See näeb parem välja. Nüüd arvutatakse meie % iga tootekategooria kogumüügist protsendina 2007. aasta kogumüügist. Kui valime kalendriaasta tükeldis muu aasta või rohkem kui ühe aasta, saame oma tootekategooriate jaoks uued protsendid, kuid meie üldsumma on endiselt 100%. Saame lisada ka muid tükeldeid ja filtreid. Meie protsent kogumüügist annab alati protsendi kogumüügist, sõltumata rakendatud tükelditest või filtritest. Meetmetega arvutatakse tulem alati vastavalt kontekstile, mis on määratud veergudes ja ridades väljadega ning rakendatud filtrite või tükeldidega. See on meetmetega seotud jõud.

Siin on mõned juhised, mis aitavad teil otsustada, kas arvutuslik veerg või mõõt on konkreetse arvutuse jaoks õige või mitte.

Arvutatud veergude kasutamine

• Kui soovite, et uued andmed kuvataks PivotTable-liigendtabelis ridadel, veergudes või filtrites või Power View visualiseeringus teljel, legendil või paanil TILE BY, peate kasutama arvutusliku veeru. Nagu tavalised andmeveerud, saab arvutuslik veerge kasutada väljana mis tahes alal ja kui need on arvulised, saab neid liita ka väärtustes.

• Kui soovite, et uued andmed oleks rea fikseeritud väärtus. Näiteks teil on kuupäevatabel koos kuupäevade veeruga ja soovite teist veergu, mis sisaldab ainult kuu numbrit. Saate luua arvutatud veeru, mis arvutab ainult kuunumbri veeru Kuupäev kuupäevadest. Näiteks =MONTH('Date'[Date]).

• Kui soovite tabelisse lisada iga rea tekstiväärtuse, kasutage arvutatud veergu. Tekstiväärtustega välju ei saa väärtustes kunagi liita. Näiteks =FORMAT('Date'[Date];"mmmm") annab meile iga kuupäeva nime tabeli Kuupäev veerus Kuupäev.

Meetmete kasutamine

• Kui arvutuse tulemus sõltub alati muudest PivotTable-liigendtabelis valitud väljadest.

• Kui teil on vaja teha keerukamaid arvutusi (nt arvutada loendus mõne sorti filtri põhjal või arvutada aasta-põhine või dispersioon), kasutage arvutatud välja.

• Kui soovite, et töövihiku maht oleks võimalikult väike ja selle jõudlus oleks võimalikult suur, looge võimalikult palju arvutusi. Paljudel juhtudel võivad kõik arvutused olla mõõdud, mis vähendavad oluliselt töövihiku mahtu ja kiirendavad värskendusaega.

Pidage meeles, et arvutatud veergude loomisel pole midagi valesti, nagu tegime veeruga Kasum, ja seejärel saate selle PivotTable-liigendtabelis või aruandes kokku koguda. See on tegelikult väga hea ja lihtne viis oma arvutustest teada saamiseks ja nende loomiseks. Kui mõistate neid kahte Power Pivoti äärmiselt võimsat funktsioone, soovite luua kõige tõhusama ja täpsema andmemudeli. Loodetavasti aitab see, mida olete siin õppinud. Seal on veel mõned väga head ressursid, mis võivad ka teid aidata. Siin on vaid mõned: DAX-i valemite kontekst, Power Pivotiliitmised ja DAX-i ressursikeskus. Kuigi see on veidi arenenum ja suunatud raamatupidamis- ja rahandustöötajatele, laaditakse Excel-i näidisandmete modelleerimine ja analüüsimine koos Microsoft Power Pivotiga koos suurepäraste andmemudelite ja valemite näidetega.