Kai pirmą kartą sužinoma, kaip naudoti "Power Pivot", dauguma vartotojų atranda realią galią, kuri tam tikru būdu suskaičiuoja arba suskaičiuoja rezultatą. Jei jūsų duomenys turi stulpelį su skaitinėmis reikšmėmis, galite lengvai jį agreguoti pasirinkdami jį "PivotTable" arba "Power View" laukų sąraše. Pagal pobūdį, nes jis yra skaitinis, jis bus automatiškai sumuojamas, vidurkis, skaičiuojamas arba bet kokio tipo agregavimas, kurį pasirenkate. Tai vadinama numanoma priemone. Numanomos priemonės puikiai tinka greitam ir paprastam agregavimui, tačiau jos turi apribojimus, o tuos apribojimus beveik visada galima įveikti naudojant aiškias priemones irapskaičiuotuosius stulpelius.
Pirmiausia pažvelkime į pavyzdį, kuriame naudojame apskaičiuojamąjį stulpelį, kad įtraukkime naują teksto reikšmę kiekvienai eilutei lentelėje, pavadintoje Produktas. Kiekvienoje lentelės Produktas eilutėje yra visa informacija apie kiekvieną mūsų parduodamą produktą. Turime stulpelių, kuriuose yra produkto pavadinimas, spalva, dydis, pardavėjo kaina ir t. t. Turime kitą susijusią lentelę, pavadintą Produkto kategorija, kurioje yra stulpelis ProductCategoryName. Norime, kad į kiekvieną produktą lentelėje Produktas būtų įtraukiamas produkto kategorijos pavadinimas iš lentelės Produkto kategorija. Lentelėje Produktas galime sukurti apskaičiuojamąjį stulpelį, pavadintą Produkto kategorija:
Mūsų naujoje produktų kategorijos formulėje naudojama funkcija RELATED DAX, kad būtų galima gauti reikšmes iš stulpelio ProductCategoryName, kuris yra susijusios produktų kategorijos lentelėje, tada įveda šias kiekvieno produkto (kiekvienos eilutės) reikšmes į lentelę Produktas.
Tai puikus pavyzdys, kaip galime naudoti apskaičiuojamąjį stulpelį norėdami įtraukti fiksuotą reikšmę kiekvienai eilutei, kurią vėliau galime naudoti "PivotTable" srityje ROWS, COLUMNS arba FILTERS arba "Power View" ataskaitoje.
Sukurkite kitą pavyzdį, kuriame norime apskaičiuoti savo produktų kategorijų pelno maržą. Tai įprastas scenarijus, net ir daugybėje mokymo priemonių. Savo duomenų modelyje turime lentelę Pardavimas, kurioje yra operacijų duomenų, ir yra ryšys tarp lentelės Pardavimas ir lentelės Produkto kategorija. 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ą atimdama reikšmes stulpelyje COGS iš stulpelio SalesAmount reikšmių, pvz.:
Dabar galime sukurti "PivotTable" ir nuvilkti lauką Produkto kategorija į COLUMNS, o mūsų naują lauką Pelnas į sritį VALUES ("PowerPivot" lentelės stulpelis yra "PivotTable" laukų sąrašo laukas). Rezultatas yra numanomas matas, pavadintas Sum of Profit. Tai agreguotas kiekvienos iš skirtingų produktų kategorijų pelno stulpelio reikšmių kiekis. Mūsų rezultatas atrodo taip:
Šiuo atveju pelnas prasmingas tik kaip laukas reikšmėse. Jei norime įtraukti pelno srityje COLUMNS, mūsų "PivotTable" atrodytų taip:
Mūsų pelno laukas neįteiks jokios naudingos informacijos, kai ji bus įdėta į sritis COLUMNS, ROWS arba FILTERS. Prasminga tik kaip agreguota reikšmė srityje VALUES.
Mes sukūrėme stulpelį, pavadintą Pelnas, kuris apskaičiuoja kiekvienos lentelės Pardavimas eilutės pelno maržą. Tada įtraukėme pelno į mūsų "PivotTable" sritį VALUES, automatiškai sukurdami netiesioginį matą, kai rezultatas skaičiuojamas kiekvienai produktų kategorijai. Jei manote, kad mes tikrai apskaičiuojame savo produktų kategorijų pelną du kartus, esate teisingi. Pirmiausia apskaičiavome kiekvienos lentelės Pardavimas eilutę pelną, tada įtraukėme Pelnas į sritį VALUES, kurioje jis buvo agreguotas pagal kiekvieną produkto kategoriją. Jei taip pat galvojate, kad mums iš tikrųjų nereikėjo sukurti apskaičiuotojo pelno stulpelio, taip pat esate teisūs. Tačiau kaip tada apskaičiuojame savo pelną nesukurdami apskaičiuoto pelno stulpelio?
Pelnas būtų tikrai geriau apskaičiuotas kaip aiškus matas.
Dabar paliksime mūsų "PivotTable" lentelės Pardavimas stulpelį Pelnas ir Produkto kategorija stulpeliuose COLUMNS ir Pelnas, kad palyginsime rezultatus.
Lentelės Pardavimas skaičiavimo srityje sukursime matą, pavadintą Bendras pelnas (kad išvengtumėte pavadinimų konfliktų). Galų gale ji duos tuos pačius rezultatus kaip ir anksčiau, bet be apskaičiuoto pelno stulpelio.
Pirma, lentelėje Pardavimas pasirinkite stulpelį SalesAmount, tada spustelėkite Automatinė suma, kad sukurtumėte aiškų "SalesAmount" matą. Atminkite, kad "Power Pivot" lentelės skaičiavimo srityje sukurtas aiškus matas. Tą patį atliekame su COGS stulpeliu. Pervardysime šiuos Total SalesAmount ir Total COGS, kad juos būtų lengviau identifikuoti.
Tada sukursime kitą priemonę naudodami šią formulę:
Bendras pelnas:=[ Total SalesAmount] – [Total COGS]
Pastaba: Taip pat galėtume parašyti formulę kaip suminis pelnas:=SUM([SalesAmount]) - SUM([COGS]), tačiau sukurdami atskirus total SalesAmount ir Total COGS matus, mes taip pat galime naudoti juos savo "PivotTable" ir mes galime naudoti juos kaip argumentus visų rūšių kitose matavimo formulėse.
Pakeitę savo naująjį bendro pelno matų formatą į valiutą, galime jį įtraukti į savo "PivotTable".
Galite matyti mūsų naująjį bendrojo pelno matą, pateikiantį tuos pačius rezultatus, kaip ir pelno apskaičiuoto stulpelio kūrimas, tada jį įdėdami į VALUES. Skirtumas yra tai, kad mūsų bendro pelno matas yra daug efektyvesnis ir daro mūsų duomenų modelį švaresnį ir paprastesnį, nes skaičiuojame tik tuo metu ir tik pasirinktus laukus savo "PivotTable". Mums iš tikrųjų nereikia to pelno apskaičiuoto stulpelio.
Kodėl ši paskutinė dalis svarbi? Apskaičiuojami 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 išteklių, nes tikrai norime apskaičiuoti savo pelną, kai pasirenkate laukus, už kuriuos norime gauti "PivotTable" pelną, pvz., produktų kategorijas, regioną arba datas.
Pažvelkime į kitą pavyzdį. Vienas iš jų, kuriame apskaičiuojamasis stulpelis sukuria rezultatus, kurie iš pirmo žvilgsnio atrodo tinkami, bet ......
Šiame pavyzdyje norime apskaičiuoti pardavimo sumas kaip viso pardavimo procentinę dalį. Savo lentelėje Pardavimas sukuriame apskaičiuotąjį stulpelį, pavadintą % pardavimo, pvz.:
Mūsų formulė nurodo: kiekvienai lentelės Pardavimai eilutei padalykite stulpelio SalesAmount sumą iš sumos SUM iš visų sumų stulpelyje SalesAmount.
Jei sukursime "PivotTable" ir įtrauksime produkto kategoriją į COLUMNS ir pažymėsime naują stulpelį Pardavimo % ir įtrauksime jį į VALUES, gausime bendrą kiekvienos produktų kategorijos pardavimo % sumą.
gerai. Tai atrodo gerai iki šiol. Tačiau įtraukite duomenų filtrus. Įtraukiame kalendorinius metus ir pasirinkite metus. Šiuo atveju pasirinkite 2007. Tai, ką mes gauname.
Iš pirmo žvilgsnio tai vis tiek gali atrodyti teisinga. Tačiau mūsų procentinės vertės iš tikrųjų turėtų būti 100 %, nes norime žinoti kiekvienos mūsų produktų kategorijos 2007 m. pardavimo procentinę dalį. Taigi, kas nutiko?
Mūsų stulpelio Pardavimas procentinė reikšmė apskaičiuota kiekvienai eilutei, kuri yra stulpelio SalesAmount reikšmė, padalyta iš visų stulpelio SalesAmount reikšmių sumos. Apskaičiuoto stulpelio reikšmės yra fiksuotos. Jie yra neįamūs kiekvienos lentelės eilutės rezultatai. Kai į "PivotTable" įtraukėme % pardavimo, ji buvo agreguota kaip visų reikšmių suma stulpelyje SalesAmount. Ši visų reikšmių suma stulpelyje Pardavimas % visada bus 100 %.
Patarimas: Būtinai perskaitykite Kontekstas DAX formulėse. Tai suteikia gerą supratimą apie eilučių lygio kontekstą ir filtro kontekstą, kurį čia aprašome.
Galime panaikinti mūsų apskaičiuotąjį stulpelį % pardavimo, nes jis mums nepadės. Vietoj to sukursime įvertinimą, kuris tinkamai apskaičiuos mūsų bendro pardavimo procentą, neatsižvelgiant į tai, kokie filtrai ar duomenų filtrai taikomi.
Prisiminti TotalSalesAmount matą, kurį sukūrėme anksčiau, tą, kuris paprasčiausiai susaisto stulpelį SalesAmount? Mes naudojome jį kaip argumentą savo bendro pelno mate ir vėl jį naudosime kaip argumentą naujame apskaičiuotame lauke.
Patarimas: Aiškių priemonių, pvz., Total SalesAmount ir Total COGS, kūrimas yra ne tik naudingas "PivotTable" arba ataskaitoje, bet ir kaip argumentas kitose priemonėse, kai reikia rezultato kaip argumento. Taip formulės tampa efektyvesnės ir lengviau skaitomos. Tai gera duomenų modeliavimo praktika.
Sukuriame naują priemonę naudodami šią formulę:
%of Total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Šioje formulėje nurodyta: Padalykite rezultatą iš sumos SalesAmount iš sumos, sumos SalesAmount be jokių stulpelių ar eilučių filtrų, išskyrus apibrėžtus "PivotTable".
Patarimas: Dax nuorodoje būtinai perskaitykite apie funkcijas CALCULATE ir ALLSELECTED.
Dabar, jei į "PivotTable" įtrauksime savo naują % viso pardavimo, gausime:
Tai atrodo geriau. Dabar kiekvienos produktų kategorijos pardavimo iš viso procentinė dalis skaičiuojama kaip 2007 m. visų pardavimų procentinė dalis. Jei pasirinksite skirtingus metus arba daugiau nei vienerius metus "CalendarYear" duomenų filtru, gausime naujų procentų mūsų produktų kategorijoms, tačiau mūsų bendroji suma vis dar yra 100 %. Taip pat galime įtraukti kitų duomenų filtrų ir filtrų. Mūsų % viso pardavimo matų visada bus bendras pardavimo procentas, neatsižvelgiant į tai, kokie duomenų filtrai ar filtrai taikomi. Naudojant priemones, rezultatas visada skaičiuojamas pagal kontekstą, nustatytą stulpeliuose COLUMNS ir ROWS, ir pagal bet kokius taikytus filtrus ar duomenų filtrus. Tai yra priemonių galia.
Toliau pateikiamos kelios gairės, kurios padės nuspręsti, ar apskaičiuotasis stulpelis ar matas tinka konkrečiam skaičiavimo poreikiui:
Apskaičiuotų stulpelių naudojimas
-
Jei norite, kad nauji duomenys būtų rodomi "PivotTable" EILUTĖSe, STULPELIUOSE arba FILTRUOSE arba "Power View" vizualizacijos AŠYJE, LEGENDOJE arba TILE BY, turite naudoti apskaičiuotąjį stulpelį. Kaip ir įprastus duomenų stulpelius, apskaičiuojamąjį stulpelį galima naudoti kaip lauką bet kurioje srityje ir, jei jie yra skaitiniai, juos taip pat galima agreguoti 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, kad kitas stulpelis, kuriame būtų tik mėnesio numeris. Galite sukurti apskaičiuojamąjį stulpelį, kuris apskaičiuoja tik mėnesio numerį iš datų stulpelyje Data. Pvz., =MONTH('Date'[Date]).
-
Jei norite į lentelę įtraukti kiekvienos eilutės teksto reikšmę, naudokite apskaičiuojamąjį stulpelį. Laukų su teksto reikšmėmis negalima agreguoti reikšmėse. Pvz., =FORMAT('Date'[Date],"mmmm") suteikia mums mėnesio pavadinimą kiekvienai datai stulpelyje Data lentelėje Data.
Naudokite priemones
-
Jei skaičiavimo rezultatas visada bus priklausomas nuo kitų laukų, kuriuos pasirenkate "PivotTable".
-
Jei reikia atlikti sudėtingesnius skaičiavimus, pvz., apskaičiuoti skaičiavimą pagal tam tikros rikiavimo filtrą, apskaičiuoti metų per metus 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 skaičiavimai gali būti matuojami, žymiai sumažinant darbaknygės dydį ir pagreitinant atnaujinimo laiką.
Atminkite, kad nėra nieko blogo kuriant apskaičiuotųjų stulpelių, kaip mes padarėme su mūsų stulpeliu Pelnas, tada suskaičiuoti jį "PivotTable" arba ataskaitoje. Tai tikrai geras ir paprastas būdas sužinoti ir kurti savo skaičiavimus. Suprasdami šias dvi itin galingas "Power Pivot" funkcijas, norėsite sukurti efektyviausią ir tikslų duomenų modelį. Tikimės, kad tai, ką čia išmokote, padeda. Yra keletas kitų tikrai puikių išteklių, kurie taip pat gali jums padėti. Štai tik keli: DAXformulių kontekstas , "Power Pivot" agregavimasir DAX išteklių centras. Be to, nors jis šiek tiek pažangesnis ir nukreiptas į apskaitos ir finansų specialistus, pelno ir nuostolių duomenų modeliavimas ir analizė su "Microsoft Power Pivot" "Excel" pavyzdyje įkeliamas su puikiu duomenų modeliavimu ir formulių pavyzdžiais.
