Pirmą kartą sužinodami, kaip naudoti "Power Pivot", dauguma vartotojų sužino, kad realioji galia yra agregavimo arba tam tikru būdu apskaičiuoja rezultatą. Jei jūsų duomenyse yra stulpelis su skaitinėmis reikšmėmis, galite lengvai juos agreguoti pažymėdami juos "PivotTable" arba "Power View" laukų sąraše. Pagal pobūdį, nes tai yra skaitinis skaičius, jis bus automatiškai sumuojamas, apskaičiuojamas vidurkis, skaičiuojamas ar bet kokio tipo agregavimas, kurį pasirenkate. Tai vadinama numanoma priemone. Netiesioginės priemonės puikiai tinka greitam ir paprastam agregavimo būdui, tačiau turi apribojimų, o tas ribas beveik visada galima įveikti aiškiais matais ir apskaičiuojamais stulpeliais.
Pirmiausia pažvelkime į pavyzdį, kuriame naudojame apskaičiuojamąjį stulpelį, kad įtrauktume naują teksto reikšmę kiekvienai lentelės, pavadintos Produktas, eilutei. Kiekvienoje lentelės Produktai eilutėje yra visa informacija apie kiekvieną mūsų parduodamą produktą. Turime stulpelius, skirtus produkto pavadinimui, spalvai, dydžiui, pardavėjo kainai ir kt. Turime kitą susijusią lentelę pavadinimu Produkto kategorija, kurioje yra stulpelis ProductCategoryName. Norime, kad į kiekvieną lentelėje Produktai esantį produktą būtų įtrauktas produkto kategorijos pavadinimas iš lentelės Produkto kategorija. Lentelėje Produktai galime sukurti apskaičiuojamąjį stulpelį, pavadintą Produkto kategorija:
Mūsų naujoje produktų kategorijos formulėje naudojama funkcija RELATED DAX, kad gautų reikšmes iš stulpelio ProductCategoryName, esančio susijusioje lentelėje Produkto kategorija, ir tada įveda kiekvieno produkto reikšmes (kiekvienoje eilutėje) lentelėje Produktas.
Tai puikus pavyzdys, kaip galime naudoti apskaičiuojamąjį stulpelį, kad įtrauktume fiksuotą reikšmę kiekvienai eilutei, kurią vėliau galime naudoti "PivotTable" srityje EILUTĖS, STULPELIAI arba FILTRAI arba "Power View" ataskaitoje.
Sukurkime kitą pavyzdį, kuriame norime apskaičiuoti mūsų produktų kategorijų pelno maržą. Tai įprastas scenarijus, net vadovėliai daug. Mūsų duomenų modelyje yra lentelė Pardavimas, kurioje yra operacijų duomenys, o tarp lentelės Pardavimas ir Produkto kategorija yra ryšys. Lentelėje Pardavimas yra stulpelis, kuriame yra pardavimo sumos, ir kitas stulpelis, kuriame yra išlaidų.
Galime sukurti apskaičiuojamąjį stulpelį, kuris apskaičiuoja kiekvienos eilutės pelno sumą atimdami stulpelio COGS reikšmes iš stulpelio "SalesAmount" reikšmių, pvz.:
Dabar galime sukurti "PivotTable" ir nuvilkti lauką Produkto kategorija į STULPELIAI, o mūsų naują lauką Pelnas į sritį REIKŠMĖS ("PowerPivot" lentelės stulpelis yra laukas "PivotTable" laukų sąraše). Gautasis rezultatas yra netiesioginis matas, pavadintas Pelno suma. Tai yra agreguota kiekvienos skirtingų produktų kategorijų reikšmių suma iš stulpelio Pelnas. Mūsų rezultatas atrodo taip:
Šiuo atveju Pelnas prasmingas tik kaip reikšmių laukas. Jei srityje STULPELIAI būtų pelnas, mūsų "PivotTable" atrodys taip:
Mūsų laukas Pelnas neteikia jokios naudingos informacijos, kai jis perkeliamas į stulpelių, eilučių arba filtrų sritis. Prasminga tik kaip agreguota reikšmė reikšmių srityje.
Tai, ką mes padarėme, yra sukurti stulpelį, pavadintą Pelnas, kuris apskaičiuoja kiekvienos lentelės Pardavimas eilutės pelno maržą. Tada į "PivotTable" sritį REIKŠMĖS įtraukėme Pelnas, automatiškai sukurdami numanomą matą, kurio rezultatas apskaičiuojamas pagal kiekvieną produktų kategoriją. Jei galvojate, kad du kartus iš tikrųjų skaičiuojame mūsų produktų kategorijų pelną, esate teisūs. Pirmiausia skaičiuojame kiekvienos lentelės Pardavimas eilutės pelną, tada įtraukėme Pelnas į sritį REIKŠMĖS, kurioje jis buvo agreguotas pagal kiekvieną produktų kategoriją. Jei taip pat galvojate, kad mums iš tikrųjų nereikia sukurti apskaičiuojamojo stulpelio Pelnas, taip pat esate teisingi. Tačiau kaip tada mes apskaičiuojame savo pelną nesukurdami apskaičiuojamojo stulpelio Pelnas?
Pelnas, iš tikrųjų būtų geriau skaičiuojamas kaip aiškus matas.
Kol kas paliksime apskaičiuojamąjį stulpelį Pelnas lentelėje Pardavimas, o Produktų kategorija – STULPELIUOSE, o Pelnas – "PivotTable" reikšmėse, kad palygintume rezultatus.
Lentelės Pardavimas skaičiavimo srityje sukursime matą, pavadintą Bendras pelnas (kad išvengtume pavadinimų suteikimo konfliktų). Galų gale jis pateiks tuos pačius rezultatus, kaip ir anksčiau, bet be apskaičiuojamojo stulpelio Pelnas.
Pirma, lentelėje Pardavimas pasirenkame stulpelį SalesAmount, tada spustelėkite Automatinė sudėtis, kad sukurtumėte aiškų matą "SalesAmount" suma. Atminkite, kad aiškus matas yra tas, kurį sukuriame "Power Pivot" lentelės skaičiavimo srityje. Tą patį darome ir SU COGS stulpeliu. Pervardysime šias Total SalesAmount ir Total COGS , kad būtų lengviau identifikuoti.
Tada mes sukursime kitą priemonę su šia formule:
Bendrasis pelnas:=[ Total SalesAmount] - [Total COGS]
Pastaba: Be to, savo formulę galime rašyti kaip Total Profit:=SUM([SalesAmount]) – SUM([COGS]), tačiau sukurdami atskirus matus Total SalesAmount ir Total COGS, juos taip pat galime naudoti "PivotTable" ir naudoti juos kaip argumentus visose kitų matų formulėse.
Pakeitę naujojo bendrojo pelno mato formatą į valiutą, galime jį įtraukti į "PivotTable".
Galite matyti, kad mūsų naujas matas Bendras pelnas pateikia tuos pačius rezultatus kaip ir kuriant stulpelį Pelnas, tada pateikiant jį reikšmėse. Skirtumas yra mūsų bendrojo pelno matas yra daug efektyvesnis, todėl mūsų duomenų modelis tampa aiškesnis ir paprastesnis, nes šiuo metu skaičiuojame tik tuos laukus, kuriuos pasirenkame savo "PivotTable". Mums tikrai nereikia apskaičiuojamojo stulpelio Pelnas.
Kodėl ši paskutinė dalis svarbi? Apskaičiuojamieji stulpeliai įtraukia duomenis į duomenų modelį, o duomenys užima atmintį. Jei atnaujinsime duomenų modelį, apdorojimo ištekliai taip pat reikalingi norint perskaičiuoti visas reikšmes stulpelyje Pelnas. Mums iš tikrųjų nereikia imtis panašių išteklių, nes tikrai norime apskaičiuoti savo pelną, kai "PivotTable" pasirenkame laukus, kurių pelną norime gauti, pvz., produktų kategorijas, regioną ar datas.
Pažvelkime į kitą pavyzdį. Vienoje vietoje, kurioje apskaičiuojamasis stulpelis sukuria rezultatus, kurie iš pirmo žvilgsnio atrodo teisingi, bet......
Šiame pavyzdyje norime apskaičiuoti pardavimo sumas kaip bendro pardavimo procentinę dalį. Lentelėje Pardavimas sukuriame apskaičiuojamąjį stulpelį, pavadintą % nuo Pardavimas , štai taip:
Mūsų formulės būsena: kiekvienoje lentelės Pardavimas eilutėje sumą stulpelyje "SalesAmount" padalinkite iš visų stulpelyje "SalesAmount" esančių sumų sumos SUM.
Jei sukuriame "PivotTable" ir įtraukiame produktų kategoriją į STULPELIUS ir pasirenkame naują stulpelį "Sales" , kad įtrauktume jį į REIKŠMES, gauname sumą, kuri sudaro % visų mūsų produktų kategorijų pardavimo.
Gerai. Tai atrodo gerai iki šiol. Tačiau įtraukime duomenų filtrą. Įtraukiame Kalendoriniai metai, tada pasirenkame metus. Šiuo atveju pasirenkame 2007. Štai ką gauname.
Iš pirmo žvilgsnio tai vis tiek gali atrodyti teisinga. Tačiau mūsų procentai iš tikrųjų turėtų sudaryti 100 %, nes norime sužinoti kiekvienos iš mūsų produktų kategorijų 2007 m. bendro pardavimo procentinę dalį. Taigi, kas nutiko?
Stulpelis "Sales" % apskaičiuoja kiekvienos eilutės, kuri yra stulpelio "SalesAmount" reikšmė, padalintą iš visų stulpelio "SalesAmount" reikšmių sumos, procentą. Apskaičiuoto stulpelio reikšmės yra fiksuotos. Jie yra nekintamų kiekvienos lentelės eilutės rezultatas. Kai į savo "PivotTable" įtraukėme % pardavimo , ji buvo agreguota kaip visų stulpelio "SalesAmount" reikšmių suma. Ši visų stulpelio Pardavimas % reikšmių suma visada bus 100 %.
Patarimas: Būtinai perskaitykite DAX formulių kontekstą. Jis puikiai supranta eilučių lygio kontekstą ir filtro kontekstą, kurį čia aprašome.
Galime panaikinti apskaičiuojamąjį stulpelį %, nes mums tai nepadės. Vietoj to sukursime matą, kuris tinkamai apskaičiuos bendro pardavimo procentą, neatsižvelgiant į pritaikytus filtrus ar duomenų filtrus.
Atsimenate anksčiau sukurtą matą TotalSalesAmount, t. y. matas, kuris tiesiog susumuoja stulpelį SalesAmount? Naudojome jį kaip argumentą bendro pelno mate ir ketiname panaudoti jį dar kartą kaip argumentą naujame apskaičiuotajame lauke.
Patarimas: Tikslių matų, pvz., Total SalesAmount ir Total COGS, kūrimas "PivotTable" ar ataskaitoje yra ne tik naudingas, bet ir naudingas kaip argumentai kituose matuose, kai rezultato reikia kaip argumento. Dėl to formulės tampa efektyvesnės ir lengviau skaitoma. Tai gera duomenų modeliavimo praktika.
Mes sukuriame naują matą pagal šią formulę:
% nuo bendro pardavimo:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Šios formulės būsenos: padalinkite rezultatą iš Total SalesAmount iš "SalesAmount" sumos be jokių stulpelių ar eilučių filtrų, kurie nenurodyti "PivotTable".
Patarimas: Būtinai perskaitykite APIE CALCULATE ir ALLSELECTED funkcijas DAX nuorodoje.
Dabar, jei įtrauksime naują % bendro pardavimo į "PivotTable", gauname:
Tai atrodo geriau. Dabar mūsų kiekvienos produktų kategorijos bendro pardavimo procentas skaičiuojamas kaip 2007 metų bendro pardavimo procentas. Jei duomenų filtre "Kalendoriniai metai" pasirenkame kitus metus arba daugiau nei vienerius metus, gauname naujus produktų kategorijų procentus, tačiau bendroji suma vis dar yra 100 %. Galime įtraukti ir kitus duomenų filtrus bei filtrus. Matų, kurių bendras pardavimas yra %, procentas visada bus taikomas neatsižvelgiant į taikomus duomenų filtrus ar filtrus. Matų rezultatas visada apskaičiuojamas pagal stulpelių ir eilučių laukų ir taikomų filtrų ar duomenų filtrų kontekstą. Tai priemonių galia.
Pateikiame keletą rekomendacijų, padėsiančių nuspręsti, ar apskaičiuojamasis stulpelis ar matas tinka konkrečiam skaičiavimo poreikiui:
Apskaičiuotų stulpelių naudojimas
-
Jei norite, kad nauji duomenys būtų rodomi EILUTĖSE, STULPELIUOSE arba FILTRUOSE "PivotTable" arba AŠYJE, LEGENDOJE arba IŠKLOTINĖS DALIS PAGAL "Power View" vizualizacijoje, turite naudoti apskaičiuojamąjį stulpelį. Kaip ir įprasti duomenų stulpeliai, apskaičiuojamieji stulpeliai gali būti naudojami kaip laukas bet kurioje srityje ir, jei jie yra skaitiniai, juos taip pat galima agreguoti naudojant VALUES.
-
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 skaičius. Galite sukurti apskaičiuojamąjį stulpelį, kuris apskaičiuoja tik mėnesio numerį pagal datas, esančias stulpelyje Data. Pavyzdžiui, =MONTH('Date'[Date]).
-
Jei norite į lentelę įtraukti kiekvienos eilutės tekstinę reikšmę, naudokite apskaičiuojamąjį stulpelį. Laukų su tekstinėmis reikšmėmis negalima agreguoti reikšmėse. Pavyzdžiui, =FORMAT('Date'[Date],"mmmm") suteikia mums kiekvienos datos mėnesio pavadinimą datos lentelės stulpelyje Data.
Matų naudojimas
-
Jei jūsų skaičiavimo rezultatas visada bus priklausomas nuo kitų "PivotTable" pasirinktų laukų.
-
Jei reikia atlikti sudėtingesnius skaičiavimus, pvz., apskaičiuoti skaičių pagal tam tikro tipo filtrą, skaičiuoti metų duomenis arba dispersiją, naudokite apskaičiuotąjį lauką.
-
Jei norite išlaikyti minimalų darbaknygės dydį ir padidinti jos našumą, sukurkite kiek įmanoma daugiau skaičiavimų. Daugeliu atvejų visi jūsų skaičiavimai gali būti matai, žymiai sumažinti darbaknygės dydį ir paspartinti atnaujinimo laiką.
Atminkite, kad nėra nieko blogo kuriant apskaičiuotuosius stulpelius, kaip tai darėme su stulpeliu Pelnas, tada agreguodami jį "PivotTable" arba ataskaitoje. Iš tikrųjų tai tikrai geras ir paprastas būdas sužinoti apie tai ir sukurti savo skaičiavimus. Suprasdami šias dvi itin efektyvias "Power Pivot" funkcijas, norėsite sukurti efektyviausią ir tikslų duomenų modelį. Tikimės, kad tai, ką išmokote čia, padeda. Yra ir kitų tikrai puikių išteklių, kurie gali jums padėti. Štai tik keletas: DAX formulių kontekstas, "Power Pivot" agregaviimai ir DAX išteklių centras. Nors jis šiek tiek pažengęs ir nukreiptas į apskaitos ir finansų specialistus, pelno ir nuostolių duomenų modeliavimas ir analizė su "Microsoft Power Pivot" programoje "Excel" įkeliamas su puikiais duomenų modeliavimo ir formulių pavyzdžiais.
