Kad pirmoreiz uzzināsit, kā izmantot power Pivot, lielākā daļa lietotāju atklāj, ka reālā jauda kādā veidā tiek apkopota vai aprēķina rezultātu. 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ā. Pēc būtības tā ir skaitliska vērtība, tāpēc tā tiks automātiski summēta, aprēķinātā vidējā vērtība, aprēķināts vai jebkāds jūsu atlasītais apkopojuma tips. Tas tiek dēvēts par netīto pasākumu. Netiešie pasākumi ir lieliski paredzēti ātrai un vienkāršai apkopošanai, taču tiem ir ierobežojumi, un šos ierobežojumus gandrīz vienmēr var atrisināt, izmantojot tiešus pasākumus un aprēķinātas kolonnas.
Vispirms apskatīsim piemēru, kurā izmantojam aprēķināto kolonnu, lai pievienotu jaunu teksta vērtību katrai tabulas Produkts rindai. Katra rinda tabulā Produkti satur visu veidu informāciju par katru pārdoto produktu. Mums ir kolonnas Produkta nosaukums, Krāsa, Lielums, Cena utt. Mums ir cita saistīta tabula ar nosaukumu Produkta kategorija, kurā ir kolonna ProductCategoryName. Kas ir nepieciešams katram produktam tabulā Produkti, lai iekļautu produktu kategorijas nosaukumu no tabulas Produktu kategorija. Mūsu tabulā Produkti varam izveidot aprēķinātu 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 kolonnas ProductCategoryName saistītajā tabulā Produktu kategorija, un pēc tam ievada šīs vērtības katram produktam (katra rinda) tabulā Produkts.
Šis ir lielisks piemērs, kā mēs varam izmantot aprēķināto kolonnu, lai pievienotu fiksētu vērtību katrai rindai, ko varam izmantot vēlāk rakurstabulas apgabalā RINDAS, KOLONNAS vai FILTRI vai Power View atskaitē.
Izveidosim vēl vienu piemēru, kurā vēlamies aprēķināt peļņas normu mūsu produktu kategorijām. Tas ir bieži sastopams scenārijs pat bieži lietotā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 apjomiem un citu kolonnu, kurā ir izmaksas.
Mēs varam izveidot aprēķinātu kolonnu, kas aprēķina katras rindas peļņas summu, 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 lauku Produkta kategorija uz KOLONNAS, bet mūsu jauno lauku Peļņa apgabalā VĒRTĪBAS (kolonna PowerPivot tabulā ir lauks rakurstabulas lauku sarakstā). Rezultāts ir netiešais mērs ar nosaukumu Peļņas summa. Tas ir apkopots vērtību apjoms no peļņas kolonnas katrai produktu kategorijai. Mūsu rezultāts izskatās šādi:
Šajā gadījumā vērtība vērtību laukā ir saprotama tikai ar vērtību lauku. Ja peļņa tiktu ielikta apgabalā KOLONNAS, rakurstabula izskatīsies šādi:
Lauks Mūsu peļņa nenodrošina noderīgu informāciju, ja tas tiek ievietots apgabalos KOLONNAS, RINDAS un FILTRI. Tas ir saprotams tikai kā apkopotā vērtība apgabalā VĒRTĪBAS.
Ir izveidota kolonna ar nosaukumu Peļņa, kas aprēķina peļņas normu katrai tabulas Pārdošana rindai. Pēc tam mēs pievienojām peļņu mūsu rakurstabulas apgabalam VĒRTĪBAS, automātiski izveidojot netīšu pasākumu, kurā tiek aprēķināts rezultāts katrai produktu kategorijai. Ja jums ir domājat, ka mēs divreiz aprēķināto peļņu mūsu produktu kategorijām, tad viss ir pareizi. Vispirms mēs 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 arī jūs domājat, ka mums nav jāizveido kolonna Aprēķinātā peļņa, arī tā ir pareiza. Bet kā tad aprēķināt savu peļņu, neizveidojot aprēķināto kolonnu Peļņa?
Peļņa tiktu tiešām labāk aprēķināta kā tieša mērs.
Pašlaik, lai salīdzinātu rezultātus, atstājiet aprēķināto kolonnu Peļņa tabulā Pārdošana un Produktu kategorija kolonnāS un Peļņa mūsu rakurstabulā VĒRTĪBAS.
Mūsu pārdošanas tabulas aprēķinu apgabalā mēs izveidosim mēru ar nosaukumu Kopējā peļņa (lai izvairītos no nosaukumdošanas konfliktiem). Beigās tas dod tādus pašus rezultātus kā iepriekš, taču neierēķina aprēķināto kolonnu Peļņa.
Vispirms tabulā Pārdošana tiek atlasīta kolonna PārdSumma un pēc tam jānoklikšķina uz Automātiskā summēšanu, lai izveidotu tiešu mēru PārdSummasumma. Atcerieties, ka tieša mērs ir tas, ko izveidojām tabulas aprēķinu apgabalā pievienojumdiagrammā Power Pivot. Tas pats tiek paveikts kolonnā COGS. Lai atvieglotu identificēšanu, mēs pārdēvēsim šīs tabulas Total SalesAmount un Total COGS .
Pēc tam, izmantojot šo formulu, izveidojam vēl vienu mēru:
Kopējā peļņa:=[ Total SalesAmount] - [Total COGS]
Piezīme.: Mēs varētu arī uzrakstīt mūsu formulu kā kopējo peļņu:=SUM([SalesAmount]) - SUM([COGS]), bet, izveidojot atsevišķus mērus Total SalesAmount un Total COGS, varam tos izmantot arī mūsu rakurstabulā, un mēs varam tos izmantot kā argumentus visās citās mērvienību formulās.
Pēc mūsu jaunā kopējās peļņas mēra formāta maiņas uz valūtu, varam to pievienot mūsu rakurstabulai.
Jūs varat redzēt, ka mūsu jaunais kopējās peļņas mērs atgriež tos pašus rezultātus kā izveidojot aprēķināto kolonnu Peļņa un pēc tam to ievietojot kolonnā VALUES. Atšķirība ir mūsu kopējās peļņas mērs ir daudz efektīvāks un padara mūsu datu modeli tīrāku un vienkāršāku, jo tos aprēķinām vienlaikus un tikai ar laukiem, ko mēs atlasām mūsu rakurstabulai. Patiesībā šī kolonna Peļņa nav jāaprēķina.
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. Atsvaidzinot datu modeli, ir nepieciešami arī apstrādes resursi, lai pārrēķinātu visas vērtības kolonnā Peļņa. Mums nav tik tiešām nepieciešams izmantot šādam resursus, jo mēs tiešām vēlamies aprēķināt savu peļņu, kad rakurstabulā atlasām laukus, kuriem mēs vēlamies izmantot peļņu, piemēram, produktu kategorijas, reģionu vai datumus.
Apskatīsim citu piemēru. Kādā aprēķinātajā kolonnā tiek izveidoti rezultāti, kas pirmajā acu uzmetienā izskatās pareizi, bet....
Šajā piemērā mēs vēlamies aprēķināt pārdošanas apjomus kā procentuālu vērtību no kopējā pārdošanas apjoma. Savā tabulā Pārdošana izveidojam aprēķināto kolonnu ar nosaukumu % no pārdošanas apjoma, piemēram:
Mūsu formulā ir norādīts: Katrai tabulas Pārdošana rindai summu kolonnā SalesAmount daliet ar visu kolonnā SalesAmount visu summu SUM.
Ja izveidojam rakurstabulu un pievienosim produktu kategoriju kolonnām un atlasām mūsu jauno kolonnu % no pārdošanas apjoma, lai to ievietotu vērtībām, iegūstam katras mūsu produktu kategorijas % no pārdošanas kopsummas.
Labi. Līdz šim tas izskatās lieliski. Taču pievienosim datu griezumu. Tiek pievienots kalendārais gads, un pēc tam tiek atlasīts gads. Šajā gadījumā mēs atlasām 2007. Mēs to iegūstam.
Pirmajā acu uzmetienā tas joprojām var izskatīties pareizi. Taču mūsu procentuālajām procentiem tiešām vajadzētu būt 100%, jo mēs vēlamies uzzināt procentuālo vērtību no kopējā pārdošanas apjoma katrai mūsu produktu kategorijai 2007. gadā. Kas notika?
Our % of Sales column calculated a percent for each row that is the value in the SalesAmount column divided by the sum total of all values in the SalesAmount column. Vērtības aprēķinātajā kolonnā ir fiksētas. Tie ir nemainīgs rezultāts katrai tabulas rindai. Kad savā rakurstabulā pievienojām % no pārdošanas apjoma, tas tika apkopots kā visu kolonnas SalesAmount vērtību summa. Šī visu kolonnas Pārdošanas % vērtību summa vienmēr būs 100%.
Padoms.: Noteikti izlasiet DAX formulu kontekstu. Tā nodrošina izpratni par rindu līmeņa kontekstu un filtra kontekstu, kas ir tas, ko mēs šeit aprakstam.
Mēs varam izdzēst aprēķināto kolonnu % no pārdošanas apjoma, jo šī kolonna mums nepalīdzēs. Tā vietā mēs izveidosim pasākumu, kas pareizi aprēķinātu mūsu procentuālo vērtību no kopējā pārdošanas apjoma neatkarīgi no lietotajiem filtriem vai datu griezumiem.
Vai atceraties iepriekš izveidoto Mērs TotalSalesAmount, kurā tiek vienkārši summēts kolonna SalesAmount? Mēs to izmantojām kā argumentu mūsu jaunajā aprēķinātajā laukā, un mēs to izmantosim vēlreiz kā argumentu mūsu jaunajā aprēķinātajā laukā.
Padoms.: Noteiktu pasākumu, piemēram, Total SalesAmount un Total COGS, izveide ir ne tikai noderīga rakurstabulā vai atskaitē, bet arī ir noderīga kā argumenti citos mēra gadījumos, kad nepieciešams rezultāts kā arguments. Tas padara formulas efektīvāku un vieglāk lasāmas. Šī ir laba datu modelēšanas prakse.
Mēs izveidojam jaunu mēru, izmantojot šādu formulu:
% no kopējā pārdošanas apjoma:=([SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Šajā formulā ir norādīts: daliet rezultātu no Total SalesAmount ar SalesAmount kopsummu bez kolonnas vai rindas filtriem, kas nav definēti rakurstabulā.
Padoms.: Noteikti izlasiet DAX atsaucē par funkcijām CALCULATE un ALLSELECTED.
Tagad, pievienojot rakurstabulai jaunos % no pārdošanas kopsummas, iegūstam:
Tas izskatās labāk. Tagad mūsu % no pārdošanas kopsummas katrai produktu kategorijai tiek aprēķināti kā procentuālā daļa no kopējā pārdošanas apjoma 2007. gadā. Ja datu griezumā CalendarYear atlasām citu gadu vai vairāk par vienu gadu, iegūstam jaunas procentuālās vērtības attiecībā uz mūsu produktu kategorijām, taču mūsu gala summa joprojām ir 100%. Mēs varam pievienot arī citus datu griezumus un filtrus. Mūsu % no pārdošanas kopsummas mērs vienmēr radīs procentuālo vērtību no kopējā pārdošanas apjoma neatkarīgi no izmantotajiem 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, un pēc lietotajiem filtriem vai datu griezumiem. Tās ir mēra pilnvaras.
Tālāk ir norādīts dažas vadlīnijas, kas palīdzēs izlemt, vai aprēķinātā kolonna vai mērs ir piemērots konkrētam aprēķinam.
Aprēķināto kolonnu izmantošana
-
Ja vēlaties, lai jaunie dati tiktu rādīti rakurstabulā ROWS, COLUMNS vai FILTERS, asī, APZĪMĒJUMOS vai TILE BY Power View vizualizācijā, izmantojiet aprēķināto kolonnu. Tāpat kā parastas datu kolonnas, arī aprēķinātās kolonnas var izmantot kā lauku jebkurā apgabalā, un, ja tās ir skaitliskas, tās var apkopot arī tabulā VALUES.
-
Ja vēlaties, lai jaunie dati rindā būtu fiksēta vērtība. Piemēram, jums ir datumu tabula ar datumu kolonnu un jūs vēlaties citu kolonnu, kurā ir tikai mēneša numurs. Varat izveidot aprēķināto kolonnu, kas aprēķina tikai mēneša numuru no datumiem kolonnā Datums. Piemēram, =MONTH('Datums'[Datums]).
-
Ja vēlaties tabulai pievienot teksta vērtību katrai rindai, izmantojiet aprēķināto kolonnu. Laukos ar teksta vērtībām vērtības nekad nevar apkopot vērtību VALUES. Piemēram, =FORMAT('Date'[Date],"mmmm") sniedz mums mēneša nosaukumu katram datumam kolonnā Datums tabulā Datums.
Izmantot pasākumus
-
Ja aprēķina rezultāts vienmēr būs atkarīgs no citiem laukiem, kurus atlasāt rakurstabulā.
-
Ja ir jāveic sarežģītāki aprēķini, piemēram, jāaprēķina skaits, izmantojot kāda veida filtru, vai arī jāaprēķina, kas ir pa gadiem, vai arī dispersija, izmantojiet aprēķināto lauku.
-
Ja vēlaties minimāli paturēt darbgrāmatas lielumu un palielināt tās veiktspēju, izveidojiet pēc iespējas vairāk aprēķinu. Daudzos gadījumos var izmantot visus aprēķinus, būtiski samazinot darbgrāmatas lielumu un paātrinot atsvaidzināšanas laiku.
Ņemiet vērā, ka ar aprēķināto kolonnu izveidi nekas nav kārtībā kā mūsu peļņas kolonnai un pēc tam to apkopot rakurstabulā vai atskaitē. Patiesībā tas ir tiešām labs un viegls veids, kā uzzināt par saviem aprēķiniem un tos izveidot. Izprotot šo divu ļoti jaudīgo Power Pivot līdzekļu augšanu, vēlēsities izveidot jums vispiemērotāko un precīzāko datu modeli. Cerams, ka šeit apgūtā informācija jums palīdzēs. Šeit ir arī daži ļoti lieliski resursi, kas var jums palīdzēt. Lūk, tikai daži: DAX formulu, apkopojumu barošanas avota unDAX resursu centra konteksts. Lai gan tā ir nedaudz sarežģītāka un tiek virzīta uz grāmatvedības un finanšu speciālistiem, datu modelēšana un analīze, izmantojot Excel programmas Microsoft Power Pivot paraugu, ir ielādēta ar lieliskiem datu modelēšanas un formulu piemēriem.
