Millal kasutada arvutuslikke veerge ja arvutuslikke välju?

Power Pivoti esmakordsel kasutamisel avastab enamik kasutajaid, et tegelik võimsus on tulemite koondamine või arvutamine. Kui teie andmetel on arvväärtustega veerg, saate selle hõlpsalt liita, valides selle PivotTable-liigendtabelis või Power View' väljaloendis. Kuna see on olemuselt arvuline, summeeritakse see automaatselt, keskmistatakse, loendatakse või valitakse mis tahes tüüpi liitmine. Seda nimetatakse kaudseks meetmeks. Kaudselt loodavad mõõdud sobivad suurepäraselt kiireks ja lihtsaks koondamiseks, kuid neil on piirangud ning neid piiranguid saab peaaegu alati ületada otseste meetmete ja arvutuslike veergudega.

Vaatame esmalt näidet, kus lisame arvutatud veeru abil tabeli "Toode" igale reale uue tekstiväärtuse. Iga tabeli Toode rida sisaldab iga müüdud toote kohta igasugust teavet. Meil on veerud Toote nimi, Värv, Suurus, Edasimüüja hind jne. Meil on veel üks seotud tabel nimega Tootekategooria, mis sisaldab veergu ProductCategoryName. Soovime, et iga tabelis Toode olev toode sisaldaks tootekategooria nime tabelist Tootekategooria. Tabelis Toode saame luua arvutatud veeru nimega Tootekategooria järgmiselt:

Meie uus tootekategooria valem kasutab funktsiooni RELATED DAX väärtuste toomiseks 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 fikseeritud väärtuse igale reale, mida saame hiljem kasutada 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 tavaline stsenaarium, isegi palju õpetusi. Meie andmemudelis on tabel Müük, mis sisaldab tehinguandmeid ning tabeli Müük ja tabeli Tootekategooria vahel on seos. Tabelis Müük on meil veerg, mis sisaldab müügisummasid ja teist kuludega veergu.

Saame luua arvutusliku veeru, mis arvutab iga rea kasumisumma, lahutades veeru COGS väärtused veeru Müügisumma väärtustest, näiteks nii:

Nüüd saame luua PivotTable-liigendtabeli ja lohistada välja Tootekategooria veergudesse ja uue välja Kasum alale VÄÄRTUSED (PowerPivoti tabeli veerg on PivotTable-liigendtabeli väljaloendi väli). Tulemuseks on kaudselt mõõt nimega Kasumi summa. See on iga tootekategooria kasumiveeru koondväärtus. Meie tulem näeb välja selline:

Praegusel juhul on väärtus Kasum väärtus ainult väärtuste väljana. Kui paigutaksime jaotisse Veerud kasumi, näeks PivotTable-liigendtabel välja selline:

Väli Kasum ei paku kasulikku teavet, kui see paigutatakse aladele COLUMNS, ROWS või FILTERS. See on mõttekas ainult liitväärtusena alas VALUES.

Oleme loonud veeru nimega Kasum, mis arvutab tabeli Müük iga rea kasumimarginaali. Seejärel lisasime oma PivotTable-liigendtabeli väärtuste alale kasum, luues automaatselt kaudse mõõdu, kus arvutatakse tulemus iga tootekategooria kohta. Kui arvate, et meie tootekategooriate kasumid on tõesti kaks korda arvutatud, on kõik korras. Arvutasime esmalt kasumi tabeli Müük iga rea kohta ja seejärel lisasime väärtusealale Kasum, kus see koondati iga tootekategooria kohta. Kui arvate ka, et me ei pidanud tegelikult veergu Kasum arvutatud looma, on ka see õige. Aga kuidas me siis oma kasumit arvutame ilma arvutatud veergu Kasum loomata?

Kasum oleks tõepoolest parem arvutada otsese meetmena.

Praegu jätame oma tulemite võrdlemiseks tabeli Müük veerud Kasum arvutatud ning Veergudes Tootekategooria ja Kasum väärtustes.

Tabeli Müük arvutusalas loome mõõdu nimega Kogukasum (et vältida nimetamiskonflikte). Lõpuks annab see samad tulemused, mis ennegi, kuid ilma arvutatud veeruta Kasum.

Esmalt valime tabelis Müük veeru Müügisumma ja seejärel klõpsame nuppu Automaatsumma, et luua selgesõnaline müügisummamõõt. Pidage meeles, et otsene mõõt on see, mille loome Power Pivoti tabeli arvutusalas. Sama teeme ka veeru COGS puhul. Nimetame need total SalesAmount ja Total COGS ümber, et neid oleks hõlpsam tuvastada.

Seejärel loome selle valemiga uue mõõdu:

Kogukasum:=[ Müügisumma kokku] - [Kogu COGS]

Pärast uue mõõdu Total Profit vormingu muutmist valuutaks saame selle pivotTable-liigendtabelisse lisada.

Näete, et meie uus mõõt Kogukasum tagastab samad tulemid, mis arvutatud veeru Kasum loomine ja seejärel paigutab selle väärtuste hulka. Erinevus on selles, et meie kogukasumi mõõt on märksa tõhusam ning muudab meie andmemudeli puhtamaks ja säästlikumaks, kuna arvutame praegu ja ainult pivotTable-liigendtabeli jaoks valitud väljade jaoks. Tegelikult pole seda veergu Kasum arvutatud vaja.

Miks on see viimane osa oluline? Arvutuslikud veerud lisavad andmeid andmemudelisse ja andmed võtavad mälu. Andmemudeli värskendamisel on kõigi veeru Kasum väärtuste ümberarvutamiseks vaja ka töötlemisressursse. Me ei pea selliseid ressursse võtma, kuna me tõesti tahame arvutada oma kasumit, kui valime PivotTable-liigendtabelis väljad, mille jaoks soovite kasumit saada (nt tootekategooriad, piirkond või kuupäevad).

Vaatame mõnda muud näidet. Arvutuslik veerg loob tulemid, mis näevad esmapilgul küll õiged välja, kuid......

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

Meie valemis on sätestatud: jagage tabeli Müük iga rea summa veerus Müügisumma veeru Müügisumma kõigi summade summaga.

Kui loome PivotTable-liigendtabeli ja lisame tootekategooria veergudesse ja valime uue veeru % müügist , et lisada see väärtuste hulka, saame summa summas % müügist iga tootekategooria kohta.

Ok. Siiani tundub see hea olevat. Aga lisame tükeldi. Lisame kalendriaasta ja seejärel valime aasta. Praegusel juhul valime 2007. See on see, mida me saame.

Esmapilgul võib see siiski olla õige. Kuid meie protsendid peaksid tegelikult kokku olema 100%, sest soovime teada iga meie 2007. Mis siis valesti läks?

Veerus % müügist arvutati iga rea jaoks protsent, mis on veeru Müügisumma väärtus jagatuna veerus Müügisumma kõigi väärtuste summaga. Arvutatud veeru väärtused on fikseeritud. Need on muudetamatu tulem tabeli iga rea kohta. Kui lisasime PivotTable-liigendtabelisse protsendi müügist , koondati see veeru Müügisumma kõigi väärtuste summana. See veeru %/Müük kõigi väärtuste summa on alati 100%.

Saame kustutada arvutusliku veeru % müügist, kuna see ei aita meid. Selle asemel loome mõõdu, mis arvutab õigesti meie protsendi kogumüügist, olenemata rakendatud filtritest või tükelditest.

Kas mäletate varem loodud mõõtu TotalSalesAmount, mis liidib lihtsalt veeru SalesAmount? Kasutasime seda oma kogukasumi mõõdus argumendina ja kasutame seda uuesti argumendina uues arvutuslikus väljal.

Loome uue mõõdu järgmise valemiga:

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

See valem määrab: jagage tulem müügisumma kogusummast ilma veeru- või reafiltriteta, mis pole PivotTable-liigendtabelis määratletud.

Nüüd, kui lisame PivotTable-liigendtabelisse oma uue protsendi kogumüügist , saame järgmist.

See näeb parem välja. Nüüd arvutatakse meie % iga tootekategooria kogumüügist protsendina 2007. Kui valime kalendriaasta tükeldis mõne muu aasta või rohkem kui ühe aasta, saame oma tootekategooriate jaoks uusi protsente, kuid meie kogusumma on endiselt 100%. Saame lisada ka muid tükeldeid ja filtreid. Meie % mõõdust "Kogumüük" annab alati protsendi kogumüügist, olenemata rakendatud tükelditest või filtritest. Mõõtude korral arvutatakse tulem alati vastavalt kontekstile, mille määravad veergude ja ridade väljad ning rakendatud filtrid või tükeldid. See on mõõtmine.

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

Arvutatud veergude kasutamine

• Kui soovite, et uued andmed kuvataks PivotTable-liigendtabelis ridades, VEERGUDEs või filtrites või Power View' visualiseeringus TELJEL, LEGENDil või PAANIL BY, peate kasutama arvutuslikku veergu. Sarnaselt tavaliste andmeveergudega saab arvutuslikke veerge kasutada mis tahes ala väljana ja kui need on arvulised, saab neid liita ka väärtustes.

• Kui soovite, et uued andmed oleksid rea fikseeritud väärtusega. Näiteks on teil kuupäevatabel kuupäevaveeruga ja soovite muud veergu, mis sisaldab ainult kuu arvu. Saate luua arvutusliku veeru, mis arvutab veeru Kuupäev kuupäevade põhjal ainult kuunumbri. Näiteks =MONTH('Kuupäev'[Kuupäev]).

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

Kasuta mõõte

• Kui arvutuse tulem sõltub alati teistest PivotTable-liigendtabelis valitud väljadest.

• Kui teil on vaja teha keerukamaid arvutusi(nt arvutada loendus mingi filtri põhjal või arvutada aastate lõikes või dispersioon), kasutage arvutuslikku välja.

• Kui soovite töövihiku mahtu hoida miinimumini ja suurendada selle jõudlust, looge võimalikult palju arvutusi. Paljudel juhtudel võivad kõik teie arvutused olla mõõdud, mis vähendavad oluliselt töövihiku mahtu ja kiirendavad värskendusaega.

Pidage meeles, et arvutuslike veergude loomisel ei ole midagi viga, nagu tegime oma veeruga Kasum ja seejärel koondasime selle PivotTable-liigendtabelisse või aruandesse. Tegelikult on see väga hea ja lihtne viis arvutuste tundmaõppimiseks ja loomiseks. Kui mõistate neid kahte väga võimsat Power Pivoti funktsiooni, soovite luua kõige tõhusama ja täpsema andmemudeli. Loodetavasti aitab see, mida olete siin õppinud. Seal on veel mõned suurepärased ressursid, mis võivad teid samuti aidata. Siin on vaid mõned näited: DAX-i valemite kontekst, Power Pivoti liitmised ja DAX-i ressursikeskus. Kuigi see on veidi arenenum ja suunatud raamatupidamis- ja finantsspetsialistidele, laaditakse Exceli lisandmooduli Microsoft Power Pivot andmete modelleerimine ja analüüs kasumi ja kahjumi andmete modelleerimise ja analüüsi abil suurepäraste andmete modelleerimise ja valemi näidetega.