Kad pirmo reizi apgūstat, kā izmantot Power Pivot, lielākā daļa lietotāju atklāj, ka patiesās iespējas ir rezultāta apkopošanā vai aprēķināšanā. Ja jūsu datos ir kolonna ar skaitliskām vērtībām, varat to vienkārši apkopot, atlasot to rakurstabulā vai Power View lauku sarakstā. Tā kā tas ir skaitlisks, tas pēc būtības tiek automātiski summēts, aprēķināts vidējais, saskaitīts vai atkarībā no tā, kāds apkopojums tiek atlasīts. To sauc par netiešo mēru. Netiešie mēri ir lieliski piemēroti ātrai un vienkāršai apkopošanai, taču tiem ir ierobežojumi, un šos ierobežojumus gandrīz vienmēr var pārvarēt ar precīziem mēriem un aprēķinātām kolonnām.
Vispirms apskatīsim piemēru, kur mēs izmantojam aprēķināto kolonnu, lai pievienotu jaunu teksta vērtību katrai rindai tabulā ar nosaukumu Produkts. Katrā produktu tabulas rindā ir visa veida informācija par katru mūsu pārdoto produktu. Mums ir kolonnas par produkta nosaukumu, krāsu, izmēru, dīlera cenu utt. Mums ir vēl viena saistīta tabula ar nosaukumu Produktu kategorija, kurā ir kolonna Produkta_kategorijas_nosaukums. Mēs vēlamies, lai katram produktam tabulā Produkti tiktu iekļauts produktu kategorijas nosaukums no tabulas Produktu kategorija. Produktu tabulā mēs varam izveidot aprēķināto kolonnu ar nosaukumu Produktu kategorija, piemēram, šādi:
Mūsu jaunajā produktu kategorijas formulā tiek izmantota funkcija RELATED DAX, lai iegūtu vērtības no saistītās produktu kategoriju tabulas kolonnas ProductCategoryName un pēc tam ievadītu šīs vērtības katram produktam (katrai rindai) tabulā Products.
Šis ir lielisks piemērs tam, kā varam izmantot aprēķināto kolonnu, lai pievienotu fiksētu vērtību katrai rindai, kuru vēlāk varam izmantot rakurstabulas apgabalā RINDAS, KOLONNAS vai FILTRI vai Power View atskaitē.
Izveidosim citu piemēru, kur mēs vēlamies aprēķināt peļņas normu mūsu produktu kategorijām. Tas ir bieži sastopams scenārijs pat daudzās apmācībās. Mūsu datu modelī ir tabula Pārdošana, kurā ir transakciju dati, un pastāv relācija starp tabulu Pārdošana un tabulu Produktu kategorija. Tabulā Pārdošana ir kolonna ar pārdošanas summām un otra kolonna ar izmaksām.
Mēs varam izveidot aprēķinātu kolonnu, kas aprēķina peļņas summu katrai rindai, atņemot vērtības kolonnā COGS no vērtībām kolonnā SalesAmount, līdzīgi šim piemēram:
Tagad varam izveidot rakurstabulu un vilkt produktu kategorijas lauku uz KOLONNAS un jauno peļņas lauku uz apgabalu VĒRTĪBAS (PowerPivot tabulas kolonna ir rakurstabulas lauks rakurstabulas lauku sarakstā). Rezultāts ir netiešais rādītājs ar nosaukumu Peļņas summa. Tā ir apkopota vērtību summa no kolonnas Peļņa katrai dažādajai produktu kategorijai. Mūsu rezultāts izskatās šādi:
Šajā gadījumā Peļņa ir jēga tikai kā lauks funkcijā VALUES. Ja apgabalā KOLONNAS ievietotu Peļņa, rakurstabula izskatītos šādi:
Mūsu lauks Peļņa nesniedz noderīgu informāciju, ja tas tiek novietots apgabalos KOLONNAS, RINDAS vai FILTRI. Tā ir jēga tikai kā apkopota vērtība apgabalā VĒRTĪBAS.
Mēs esam izveidojuši kolonnu ar nosaukumu Peļņa, kas aprēķina peļņas normu katrai tabulas Pārdošana rindai. Pēc tam rakurstabulas apgabalam VĒRTĪBAS pievienojām vērtību vērtību, automātiski izveidojot netiešo rādītāju, kura rezultāts tiek aprēķināts katrai produktu kategorijai. Ja jūs domājat, ka mēs patiešām divreiz aprēķinājām peļņu mūsu produktu kategorijām, jums ir taisnība. Vispirms aprēķinājām peļņu katrai tabulas Pārdošana rindai un pēc tam pievienojām peļņu apgabalam VĒRTĪBAS, kur tā tika apkopota katrai produktu kategorijai. Ja domājat, ka mums īsti nevajadzēja izveidot aprēķinātās peļņas kolonnu, tad jums arī ir taisnība. Bet kā tad mēs aprēķinām savu peļņu, neizveidojot peļņas aprēķināto kolonnu?
Peļņa, tiešām būtu labāk aprēķināts kā skaidrs mērs.
Lai pagaidām salīdzinātu rezultātus, kolonnu Aprēķinātā peļņa atstāsim tabulā Pārdošana, produktu kategorijā KOLONNĀS un Peļņa rakurstabulas VĒRTĪBĀS.
Lai izvairītos no nosaukumu piešķiršanas konfliktiem, tabulas Pārdošana aprēķinu apgabalā izveidosim mēru Kopējā peļņa . Galu galā tas dos tādus pašus rezultātus kā iepriekš, bet bez peļņas aprēķinātās kolonnas.
Vispirms tabulā Pārdošana atlasiet kolonnu SalesAmount un pēc tam noklikšķiniet uz Automātiskā summēšana, lai izveidotu skaidru mēru Summa SalesAmount . Atcerieties, ka precīzs mērs ir tāds, ko mēs izveidojam pievienojumprogrammas Power Pivot tabulas aprēķinu apgabalā. Mēs darām to pašu ar COGS kolonnu. Lai tās būtu vieglāk identificēt, mēs pārdēvēsim šīs Total SalesAmount un Total COGS .
Tad mēs izveidojam vēl vienu pasākumu ar šo formulu:
Kopējā peļņa:=[Total SalesAmount] - [Total COGS]
Piezīme
Savu formulu varētu arī rakstīt kā Total Profit:=SUM([SalesAmount]) - SUM([COGS]), taču, izveidojot atsevišķus Total SalesAmount un Total COGS mērus, mēs varam tos izmantot arī mūsu rakurstabulā, un mēs varam izmantot tos kā argumentus visdažādākajās citās mēru formulās.
Pēc jaunā kopējās peļņas mēra formāta maiņas uz valūtu varam to pievienot rakurstabulai.
Varat redzēt, ka mūsu jaunais kopējās peļņas mērs atgriež tos pašus rezultātus, kādi tiek iegūti, izveidojot aprēķinātu peļņas kolonnu un pēc tam ievietojot to vērtību laukā VĒRTĪBAS. Atšķirība ir tāda, ka mūsu kopējās peļņas mērs ir daudz efektīvāks un padara mūsu datu modeli pārskatāmāku un vienkāršāku, jo mēs veicam aprēķinus tajā laikā un tikai tiem laukiem, kurus atlasām mūsu rakurstabulai. Galu galā šī peļņas aprēķinātā kolonna mums nav vajadzīga.
Kāpēc šī pēdējā daļa ir svarīga? Aprēķinātās kolonnas pievieno datus datu modelim, un dati aizņem atmiņu. Ja datu modelis tiek atsvaidzināts, ir nepieciešami arī apstrādes resursi, lai pārrēķinātu visas kolonnas Peļņa vērtības. Mums nav īsti jāizmanto šādi resursi, jo, atlasot laukus, kuriem rakurstabulā vēlamies noteikt peļņu, piemēram, produktu kategorijas, reģioni vai pēc datumiem, mums ir jāaprēķina sava peļņa.
Apskatīsim citu piemēru. Tāda, kurā aprēķinātā kolonna rada rezultātus, kas no pirmā acu uzmetiena izskatās pareizi, bet...
Šajā piemērā mēs vēlamies aprēķināt pārdošanas apjomus kā procentus no kopējā pārdošanas apjoma. Mēs izveidojam aprēķināto kolonnu ar nosaukumu % no pārdošanas apjoma mūsu pārdošanas tabulā , apmēram šādi:
Mūsu formulā teikts: Katrai tabulas Pārdošana rindai izdaliet summu kolonnā SalesAmount ar visu kolonnā SalesAmount norādīto summu SUM.
Ja izveidojam rakurstabulu, pievienojam produktu kategoriju kolonnām KOLONNĀM un atlasām jauno kolonnu % no pārdošanas apjoma , lai to ievietotu vērtībā VĒRTĪBAS, iegūstam pārdošanas apjoma kopsummu katrai mūsu produktu kategorijai.
Labi. Pagaidām tas izskatās labi. Tomēr pievienosim datu griezumu. Mēs pievienojam Calendar Year un pēc tam izvēlamies gadu. Šajā gadījumā mēs izvēlamies 2007. gadu. Tas ir tas, ko mēs iegūstam.
Pirmajā brīdī tas joprojām var šķist pareizi. Bet mūsu procentuālajiem rādītājiem patiešām vajadzētu būt 100%, jo mēs vēlamies uzzināt procentus no kopējā pārdošanas apjoma katrai mūsu produktu kategorijai 2007. gadā. Kas tad nogāja greizi?
Mūsu kolonna % no pārdošanas aprēķināja procentus katrai rindai, kas ir kolonnas SalesAmount vērtība, dalīta ar visu kolonnā SalesAmount esošo vērtību kopsummu. Aprēķinātās kolonnas vērtības ir fiksētas. Tas ir nemainīgs rezultāts katrai tabulas rindai. Kad rakurstabulai pievienojām % no pārdošanas apjoma , tas tika apkopots kā visu kolonnas SalesAmount vērtību summa. Visu kolonnā Pārdoto procentu vērtību summa vienmēr būs 100%.
Padoms
Noteikti izlasiet kontekstu DAX formulās. Tas ļauj labi izprast rindas līmeņa kontekstu un filtra kontekstu, ko mēs šeit aprakstām.
Mēs varam izdzēst aprēķināto kolonnu % no pārdošanas, jo tas mums nepalīdzēs. Tā vietā izveidosim rādītāju, kas pareizi aprēķina mūsu kopējo pārdošanas apjomu procentos neatkarīgi no lietotajiem filtriem vai datu griezumiem.
Vai atceraties iepriekš izveidoto mēru TotalSalesAmount, kas vienkārši summē kolonnu SalesAmount? Mēs to izmantojām kā argumentu mūsu kopējās peļņas mērā un izmantosim to atkal kā argumentu mūsu jaunajā aprēķinātajā laukā.
Padoms
Precīzu mēru, piemēram, Total SalesAmount un Total COGS, izveide ir noderīga ne tikai rakurstabulā vai atskaitē, bet tie ir noderīgi arī kā argumenti citos mēros, kad rezultāts ir nepieciešams kā arguments. Tas padara formulas efektīvākas un vieglāk lasāmas. Tā ir laba datu modelēšanas prakse.
Mēs izveidojam jaunu mēru ar šādu formulu:
% no kopējā realizācijas apjoma:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Šajā formulā ir teikts: Radaliet rezultātu no Total SalesAmount ar SalesAmount kopsummu, neizmantojot citus kolonnu vai rindu filtrus, izņemot tos, kas definēti rakurstabulā.
Padoms
Noteikti izlasiet informāciju par funkcijām CALCULATE un ALLSELECTED DAX atsaucēs.
Tagad, ja rakurstabulai pievienojam jauno % no kopējā pārdošanas apjoma , iegūstam:
Tas izskatās labāk. Tagad mūsu % no kopējā pārdošanas apjoma katrai produktu kategorijai tiek aprēķināta kā procentuālā daļa no kopējā pārdošanas apjoma 2007. gadā. Atlasot citu gadu vai vairāk nekā vienu gadu datu griezumā CalendarYear, iegūstam jaunus procentus mūsu produktu kategorijām, bet mūsu kopsumma joprojām ir 100%. Varam pievienot arī citus datu griezumus un filtrus. Mūsu mērījums % no kopējā pārdošanas apjoma vienmēr radīs procentuālu vērtību no kopējā pārdošanas apjoma neatkarīgi no lietotajiem datu griezumiem vai filtriem. Izmantojot mērus, rezultāts vienmēr tiek aprēķināts atbilstoši kontekstam, ko nosaka lauki kolonnās un rindās, kā arī lietotie filtri vai datu griezumi. Tas ir pasākumu spēks.
Tālāk ir sniegtas dažas vadlīnijas, kas jums palīdzēs izlemt, vai aprēķinātā kolonna vai mērs ir piemērots konkrētām aprēķina vajadzībām.
Aprēķināto kolonnu izmantošana
- Ja vēlaties, lai jaunie dati tiktu parādīti rakurstabulas RINDĀS, KOLONNĀS vai FILTROS vai Power View vizualizācijas sadaļā ASS, APZĪMĒJUMI vai MOZAĪKOT PĒC, izmantojiet aprēķināto kolonnu. Līdzīgi kā parastas datu kolonnas, aprēķinātās kolonnas var izmantot kā lauku jebkurā apgabalā, un, ja tās ir skaitliskas, tās var apkopot arī kā vērtības.
- Ja vēlaties, lai jaunie dati būtu fiksēta rindas vērtība. Piemēram, jums ir datumu tabula ar datumu kolonnu, bet jūs vēlaties citu kolonnu, kurā ir tikai mēneša numurs. Varat izveidot aprēķinātu kolonnu, kas aprēķina tikai mēneša numuru no datumiem kolonnā Datums. Piemēram, =MONTH('Datums'[Datums]).
- Ja katrai rindai vēlaties tabulai pievienot teksta vērtību, izmantojiet aprēķināto kolonnu. Laukus ar teksta vērtībām nekad nevar apkopot funkcijā VALUES. Piemēram, =FORMAT('Date'[Date],"mmmm") dod mums mēneša nosaukumu katram datumam datuma kolonnā Datums tabulā.
Mēru izmantošana
- Ja aprēķina rezultāts vienmēr būs atkarīgs no citiem rakurstabulā atlasītajiem laukiem.
- Ja jāveic sarežģītāki aprēķini, piemēram, jāaprēķina skaits, izmantojot kāda veida filtru, vai jāaprēķina gada griezums vai novirze, izmantojiet aprēķināto lauku.
- Ja vēlaties samazināt darbgrāmatas lielumu pēc iespējas mazāk un maksimāli palielināt tās veiktspēju, izveidojiet pēc iespējas vairāk aprēķinu. Daudzos gadījumos visi aprēķini var būt mēri, kas ievērojami samazina darbgrāmatas lielumu un paātrina atsvaidzināšanas laiku.
Ņemiet vērā, ka aprēķināto kolonnu izveide kā kolonnā Peļņa un pēc tam to apkopošana rakurstabulā vai atskaitē nav nekas slikts. Tas ir patiešām labs un vienkāršs veids, kā uzzināt un izveidot savus aprēķinus. Pieaugot izpratnei par šiem diviem ārkārtīgi jaudīgajiem Power Pivot līdzekļiem, vēlēsities izveidot visefektīvāko un precīzāko datu modeli. Cerams, ka šeit apgūtais palīdzēs. Ir arī daži citi patiešām lieliski resursi, kas var jums palīdzēt. Tālāk ir norādītas dažas iespējas. Konteksts DAX formulās, apkopojumi pievienojumprogrammā Power Pivot un DAX resursu centrs. Un, lai gan tas ir nedaudz progresīvāks un paredzēts grāmatvedības un finanšu profesionāļiem, peļņas un zaudējumu datu modelēšanas un analīzes ar Microsoft Power Pivot programmā Excel paraugs ir ielādēts ar lieliskiem datu modelēšanas un formulu piemēriem.