Pirmą kartą mokydamiesi naudoti "Power Pivot", dauguma vartotojų supranta, kad tikroji galia yra rezultato agregavimas arba apskaičiavimas. Jei duomenyse yra stulpelis su skaitinėmis reikšmėmis, galite lengvai juos agreguoti pasirinkdami juos "PivotTable" arba "Power View" laukų sąraše. Pagal savo pobūdį, kadangi jis yra skaitinis, jis bus automatiškai sudėtas, vidurkis, skaičiuojamas arba koks nors pasirinktas agregavimo tipas. Tai vadinama numanoma priemone. Numanomi matai puikiai tinka greitai ir lengvai agreguoti, tačiau jie turi ribas ir tas ribas beveik visada galima įveikti naudojant aiškius matus ir apskaičiuojamuosius stulpelius.
Pirmiausia pažvelkime į pavyzdį, kuriame rodoma, kaip apskaičiuojamasis stulpelis įtraukiame naują teksto reikšmę kiekvienai lentelės, pavadintos Produktas, eilutei. Kiekvienoje lentelės Produktai eilutėje yra įvairios informacijos apie kiekvieną mūsų parduodamą produktą. Turime stulpelius produkto pavadinimui, spalvai, dydžiui, pardavėjo kainai ir kt. Turime kitą susijusią lentelę, pavadintą Produkto kategorija, kurioje yra stulpelis ProduktoKategorijosPavadinimas. Mes norime, kad į kiekvieną produktų lentelėje Produktai būtų įtrauktas produkto kategorijos pavadinimas iš lentelės Produktų kategorijos. Mūsų lentelėje Produktai galime sukurti apskaičiuojamąjį stulpelį, pavadintą Produktų kategorija, kaip parodyta toliau:
Mūsų nauja produktų kategorijų formulė naudoja funkciją RELATED DAX, kad gautų reikšmes iš stulpelio ProductCategoryName susijusių produktų kategorijų lentelėje ir tada įveda tas kiekvieno produkto (kiekvienos eilutės) reikšmes lentelėje Produktai.
Tai puikus pavyzdys, kaip galime naudoti apskaičiuojamąjį stulpelį, kad įtrauktume fiksuotą kiekvienos eilutės reikšmę, kurią vėliau galėsime naudoti "PivotTable" srityje EILUTĖS, STULPELIAI ar FILTRAI arba "Power View" ataskaitoje.
Sukurkime dar vieną pavyzdį, kuriame norime apskaičiuoti savo produktų kategorijų pelno maržą. Tai įprastas scenarijus, netgi daugelyje mokymo programų. Mūsų duomenų modelyje yra lentelė Pardavimas, kurioje yra operacijų duomenys ir yra ryšys tarp lentelės Pardavimas ir lentelės Produktų kategorija. Lentelėje Pardavimai yra stulpelis su pardavimo sumomis ir kitas stulpelis su išlaidomis.
Galime sukurti apskaičiuojamąjį stulpelį, kuris apskaičiuoja kiekvienos eilutės pelno sumą, atimdamas COGS stulpelio reikšmes iš stulpelio SalesAmount reikšmių, kaip parodyta čia:
Dabar galime sukurti "PivotTable" ir nuvilkti lauką Produkto kategorija į STULPELIUS, tada naują lauką Pelnas į sritį REIKŠMĖS ("PowerPivot" lentelės stulpelis yra laukas "PivotTable" laukų sąraše). Rezultatas yra netiesioginis matas, vadinamas pelno suma. Tai bendra kiekvienai produktų kategorijai pelno stulpelyje esančių reikšmių suma. Mūsų rezultatas atrodo taip:
Šiuo atveju Pelnas prasmingas tik kaip laukas funkcijoje REIKŠMĖS. Jei Pelną įtrauktume srityje STULPELIAI, mūsų "PivotTable" atrodytų taip:
Mūsų pelno laukas neteikia jokios naudingos informacijos, kai jis yra stulpelių, eilučių ar filtrų srityse. Ji prasminga tik kaip agreguota reikšmė srityje REIKŠMĖS.
Mes sukūrėme stulpelį, pavadintą Pelnas, kuris apskaičiuoja kiekvienos lentelės Pardavimai eilutės pelno maržą. Tada į "PivotTable" sritį REIKŠMĖS įtraukėme pelną, automatiškai sukurdami netiesioginį matą, kai apskaičiuojamas kiekvienos produktų kategorijos rezultatas. Jei manote, kad mes tikrai du kartus apskaičiavome pelną savo produktų kategorijoms, esate teisus. Iš pradžių suskaičiavome kiekvienos lentelės "Sales" eilutės pelną, tada pridėjome pelną į sritį REIKŠMĖS, kurioje jis buvo agreguotas pagal kiekvieną produktų kategoriją. Jei taip pat manote, kad mums tikrai nereikia kurti stulpelio Apskaičiuotas pelnas, taip pat esate teisus. Bet kaip tada apskaičiuoti savo pelną nesukuriant pelno apskaičiuoto stulpelio?
Pelnas tikrai būtų geriau apskaičiuotas kaip aiškus matas.
Šiuo metu stulpelį Apskaičiuotas pelnas paliksime lentelėje Pardavimai, o produkto kategoriją – "PivotTable" stulpeliuose, o Pelną – REIKŠMĖSE, kad palygintume rezultatus.
Lentelės Pardavimai skaičiavimo srityje sukursime matą pavadinimu Bendrasis pelnas (kad būtų išvengta pavadinimų konfliktų). Galų gale ji duos tuos pačius rezultatus, kaip ir anksčiau, tik be pelno apskaičiuoto stulpelio.
Pirmiausia lentelėje "Sales" pasirenkame stulpelį "SalesAmount", tada spustelėkite "AutoSum", kad būtų sukurtas aiškus "SalesAmount " matas. Atminkite, kad aiškus matas yra tas, kurį sukuriame "Power Pivot" lentelės skaičiavimo srityje. Tą patį darome su COGS stulpeliu. Pervardysime šias Total SalesAmount ir Total COGS , kad būtų lengviau identifikuoti.
Tada mes sukuriame kitą priemonę pagal šią formulę:
Bendras pelnas:=[Total SalesAmount] - [Total COGS]
Pastaba
Taip pat galėtume rašyti savo formulę kaip Total Profit:=SUM([SalesAmount]) - SUM([COGS]), tačiau sukūrę atskirus Total SalesAmount ir Total COGS matus, galime juos naudoti ir savo "PivotTable", taip pat galime naudoti juos kaip argumentus įvairiose kitose matų formulėse.
Pakeitę naujo bendrojo pelno matavimo formatą į valiutą, galime jį įtraukti į savo "PivotTable".
Galite matyti, kaip mūsų naujasis bendrojo pelno matas pateikia tuos pačius rezultatus, kaip sukūrus apskaičiuojamąjį pelno stulpelį ir jį įdedant į VERTES. Skirtumas tas, kad mūsų bendro pelno matas yra daug efektyvesnis ir padaro mūsų duomenų modelį aiškesnį ir paprastesnį, nes skaičiuojame tuo metu ir tik tiems laukams, kuriuos pasirenkame savo "PivotTable". Galų gale, mums to stulpelio Pelno apskaičiavimas tikrai nereikia.
Kodėl pastaroji dalis svarbi? Apskaičiuojamieji stulpeliai įtraukia duomenis į duomenų modelį ir užima atmintį. Jei atnaujinsime duomenų modelį, apdorojimo ištekliai taip pat reikalingi norint perskaičiuoti visas stulpelio Pelnas reikšmes. Mums tikrai nereikia naudoti tokių išteklių, nes tikrai norime apskaičiuoti savo pelną, kai pasirenkame laukus, kurių pelną norime gauti "PivotTable", pvz., produktų kategorijas, regioną ar pagal datas.
Pažvelkime į kitą pavyzdį. Tokią, kurioje apskaičiuojamasis stulpelis sukuria rezultatus, kurie iš pirmo žvilgsnio atrodo teisingi, bet....
Šiame pavyzdyje norime apskaičiuoti pardavimo sumas kaip bendro pardavimo procentą. Lentelėje Pardavimai sukuriame apskaičiuojamąjį stulpelį, pavadintą % of Sales , kaip parodyta čia:
Mūsų formulė nurodo: Kiekvienoje lentelės "SalesAmount" eilutėje esančią sumą padalinkite iš stulpelyje "SalesAmount" esančių visų stulpelyje "SalesAmount" esančių sumų sumos SUM.
Jei sukursime "PivotTable" ir įtrauksime produkto kategoriją į STULPELIUS ir pasirinksime naują stulpelį % pardavimų , kad jį įtrauktume į VERTES, gausime bendrą kiekvienos produktų kategorijos pardavimo % sumą.
Gerai. Kol kas tai atrodo gerai. Tačiau prijunkime duomenų filtrą. Pridedame kalendorinius metus, tada pasirenkame metus. Šiuo atveju mes pasirenkame 2007 m. Štai ką mes gauname.
Iš pirmo žvilgsnio tai vis tiek gali atrodyti teisinga. Tačiau mūsų procentai iš tikrųjų turėtų būti 100 %, nes norime sužinoti kiekvienos iš mūsų produktų kategorijų 2007 m. visų pardavimų procentą. Taigi, kas nutiko?
Mūsų stulpelyje "% of Sales" buvo apskaičiuota kiekvienos eilutės procentinė reikšmė, kuri yra stulpelio "SalesAmount" reikšmė, padalyta iš visų stulpelyje "SalesAmount" esančių reikšmių sumos. Reikšmės apskaičiuotame stulpelyje yra fiksuotos. Jie yra nekintami kiekvienos lentelės eilutės rezultatai. Kai į "PivotTable" įtraukėme pardavimo procentą , jis buvo agreguotas kaip visų stulpelyje "SalesAmount" esančių reikšmių suma. Visų stulpelio "Pardavimo %" reikšmių suma visada bus 100 %.
Patarimas
Būtinai perskaitykite DAX formulių kontekstą. Tai leidžia gerai suprasti eilutės lygio kontekstą ir filtro kontekstą, ką čia aprašome.
Galime panaikinti apskaičiuojamąjį stulpelį "% of Sales", nes tai mums nepadės. Vietoj to sukursime matą, kuris teisingai apskaičiuos mūsų bendro pardavimo procentą, neatsižvelgiant į taikomus filtrus arba duomenų filtrus.
Prisimenate anksčiau sukurtą matą "TotalSalesAmount", kuris tiesiog susumuoja stulpelį "SalesAmount"? Mes naudojome jį kaip argumentą savo bendro pelno matavime ir ketiname vėl jį naudoti kaip argumentą savo naujame apskaičiuotajame lauke.
Patarimas
Aiškių matų, tokių kaip "Total SalesAmount" ir "Total COGS", kūrimas yra naudingas ne tik "PivotTable" ar ataskaitoje, bet taip pat naudingas kaip argumentai kituose matuose, kai rezultatą reikia kaip argumento. Dėl to formulės tampa efektyvesnės ir lengviau skaitomos. Tai gera duomenų modeliavimo praktika.
Mes sukuriame naują priemonę pagal šią formulę:
% nuo bendro pardavimo:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Ši formulė nurodo: Dalinti rezultatą iš Total SalesAmount sumos iš SalesAmount sumos be jokių stulpelių ar eilučių filtrų, išskyrus tuos, kurie apibrėžti "PivotTable".
Patarimas
Būtinai perskaitykite apie funkcijas CALCULATE ir ALLSELECTED DAX nuorodoje.
Dabar, jei į "PivotTable" įtrauksime naują bendro pardavimo procentą , gausime:
Tai atrodo geriau. Dabar kiekvienos produktų kategorijos bendro pardavimo procentas yra apskaičiuojamas kaip 2007 metų bendro pardavimo procentas. Jei duomenų filtre "Kalendoriniai metai" pasirinksime kitus metus arba daugiau nei vienerius metus, gausime naujus produktų kategorijų procentus, tačiau bendroji suma vis tiek bus 100 %. Galime įtraukti ir kitų duomenų filtrų bei filtrų. Mūsų bendro pardavimo % matavimo priemonė visada pateiks bendro pardavimo procentą, neatsižvelgiant į taikomus duomenų filtrus ar filtrus. Naudojant matus, rezultatas visada skaičiuojamas pagal kontekstą, kurį nustato laukai, esantys COLUMNS ir ROWS, bei visi taikomi filtrai arba duomenų filtrai. Tai yra priemonių galia.
Pateikiame keletą gairių, kurios padės nuspręsti, ar apskaičiuojamasis stulpelis arba matas tinka konkrečiam skaičiavimo poreikiui:
Apskaičiuojamųjų stulpelių naudojimas
- Jei norite, kad nauji duomenys būtų rodomi "PivotTable" eilutėse, stulpeliuose ar filtruose arba "Power View" vizualizacijos AŠYJE, LEGENDOJE ar IŠKLOTINĖS PAGAL, turite naudoti apskaičiuojamąjį stulpelį. Kaip įprastus duomenų stulpelius, apskaičiuojamuosius stulpelius galima naudoti kaip lauką bet kurioje srityje, o jei jie yra skaitiniai, jie taip pat gali būti agreguojami reikšmėse.
- Jei norite, kad nauji duomenys būtų fiksuota eilutės reikšmė. Pavyzdžiui, turite datų lentelę su datų stulpeliu ir norite kito stulpelio, kuriame būtų tik mėnesio numeris. Galite sukurti apskaičiuojamąjį stulpelį, kuris apskaičiuoja tik mėnesio numerį pagal datos stulpelyje nurodytas datas. Pavyzdžiui, =MONTH('Date'[Date]).
- Jei norite į lentelę įtraukti kiekvienos eilutės tekstinę reikšmę, naudokite apskaičiuojamąjį stulpelį. Laukų su teksto reikšmėmis niekada negalima agreguoti VALUES. Pvz., =FORMAT('Date'[Date],"mmmm") suteikia kiekvienos datos mėnesio pavadinimą datos lentelės stulpelyje Data .
Naudokite priemones
- Ar jūsų skaičiavimo rezultatas visada priklausys nuo kitų laukų, kuriuos pasirinksite "PivotTable".
- Jei reikia atlikti sudėtingesnius skaičiavimus, pvz., skaičiuoti skaičiavimą pagal kokios nors rūšies filtrą, metų duomenų skaičiavimus arba dispersiją, naudokite apskaičiuotąjį lauką.
- Jei norite sumažinti darbaknygės dydį ir maksimaliai padidinti jos našumą, sukurkite kuo daugiau skaičiavimų. Daugeliu atvejų visi jūsų skaičiavimai gali būti matavimai, žymiai sumažinantys darbaknygės dydį ir pagreitinantys atnaujinimo laiką.
Atminkite, nėra nieko blogo kurti apskaičiuojamuosius stulpelius, kaip tai darėme su stulpeliu Pelnas, o tada juos agreguoti "PivotTable" arba ataskaitoje. Tai tikrai geras ir paprastas būdas sužinoti apie skaičiavimus ir juos kurti. Didėjant jūsų supratimui apie šias dvi itin efektyvias "Power Pivot" funkcijas, norėsite sukurti efektyviausią ir tiksliausią duomenų modelį. Tikimės, kad tai, ką čia sužinojote, padės. Yra keletas kitų tikrai puikių išteklių, kurie gali padėti ir jums. Štai keletas iš jų: Kontekstas DAX formulėse, telkimai "Power Pivot" ir DAX išteklių centre. Ir nors jis yra šiek tiek pažangesnis ir skirtas apskaitos ir finansų specialistams, pelno ir nuostolio duomenų modeliavimo ir analizės naudojant "„Microsoft“ Power Pivot" programoje "Excel" pavyzdys yra įkeltas puikių duomenų modeliavimo ir formulių pavyzdžių.