Când se utilizează coloane calculate și câmpuri calculate

Atunci când învață pentru prima dată cum să utilizeze Power Pivot, majoritatea utilizatorilor descoperă că puterea reală este în agregare sau în calcularea unui rezultat într-un fel. Dacă datele au o coloană cu valori numerice, le puteți agrega cu ușurință selectându-le într-o listă de câmpuri PivotTable sau Power View. Prin natura sa, deoarece este numerică, va fi însumată automat, va fi calculată în medie, contorizată sau orice tip de agregare selectați. Aceasta este cunoscută ca o măsură implicită. Măsurile implicite sunt foarte bune pentru agregare rapidă și ușoară, dar au limite, iar aceste limite pot fi depășite aproape întotdeauna cu măsuriexplicite și coloane calculate.

Să vedem mai întâi un exemplu în care utilizăm o coloană calculată pentru a adăuga o nouă valoare text pentru fiecare rând dintr-un tabel denumit Produs. Fiecare rând din tabelul Produs conține tot felul de informații despre fiecare produs pe care îl vindem. Avem coloane pentru Nume produs, Culoare, Dimensiune, Preț dealer etc.. Avem un alt tabel asociat denumit Categorie produs care conține o coloană ProductCategoryName. Dorim ca fiecare produs din tabelul Produs să includă numele categoriei de produse din tabelul Categorie produs. În tabelul nostru Produs, putem crea o coloană calculată denumită Categorie produs, astfel:

Noua formulă Categorie produs utilizează funcția DAX ASOCIATĂ pentru a obține valori din coloana ProductCategoryName din tabelul asociat Categorie produs, apoi introduce valorile respective pentru fiecare produs (fiecare rând) în tabelul Produs.

Acesta este un exemplu foarte bun despre cum putem utiliza o coloană calculată pentru a adăuga o valoare fixă pentru fiecare rând pe care îl putem utiliza mai târziu în zona RÂNDURI, COLOANE sau FILTRE din PivotTable sau într-un raport Power View.

Să creăm un alt exemplu în care dorim să calculăm o marjă de profit pentru categoriile noastre de produse. Acesta este un scenariu obișnuit, chiar și într-o mulțime de tutoriale. Avem un tabel Vânzări în modelul nostru de date care conține date de tranzacție și există o relație între tabelul Vânzări și tabelul Categorie produs. În tabelul Vânzări, avem o coloană care conține sumele de vânzări și o altă coloană care are costuri.

Putem crea o coloană calculată care calculează valoarea profitului pentru fiecare rând scăzând valorile din coloana COGS din valorile din coloana VolumVânzări, astfel:

Acum putem crea un raport PivotTable și putem glisa câmpul Categorie produs în COLOANE, iar noul câmp Profit în zona VALORI (o coloană dintr-un tabel din PowerPivot este un câmp din lista de câmpuri PivotTable). Rezultatul este o măsură implicită denumită Sumă profit. Este o valoare agregată de valori din coloana de profit pentru fiecare dintre diferitele categorii de produse. Rezultatul nostru arată astfel:

În acest caz, Profit are sens doar ca un câmp în VALUES. Dacă am plasa Profit în zona COLOANE, raportul nostru PivotTable ar arăta astfel:

Câmpul profit nu oferă informații utile atunci când este plasat în zonele COLOANE, RÂNDURI sau FILTRE. Are sens doar ca valoare agregată în zona VALORI.

Am creat o coloană denumită Profit care calculează o marjă de profit pentru fiecare rând din tabelul Vânzări. Apoi am adăugat Profit la zona VALORI din Raportul PivotTable, creând automat o măsură implicită, unde se calculează un rezultat pentru fiecare dintre categoriile de produse. Dacă vă gândiți că am calculat de două ori profitul real pentru categoriile noastre de produse, sunteți corect. Am calculat mai întâi un profit pentru fiecare rând din tabelul Vânzări, apoi am adăugat Profit la zona VALORI, unde a fost agregat pentru fiecare dintre categoriile de produse. Dacă vă gândiți, de asemenea, că nu a fost nevoie să creăm coloana calculată Profit, sunteți, de asemenea, corect. Dar cum calculăm profitul fără a crea o coloană calculată profit?

Profit, ar fi într-adevăr mai bine calculată ca o măsură explicită.

Momentan, vom lăsa coloana calculată Profit în tabelul Vânzări și Categoria de produse în COLOANE și Profit în VALORI din PivotTable, pentru a ne compara rezultatele.

În zona de calcul a tabelului Vânzări, vom crea o măsură denumită Profit total (pentru a evita conflictele de nume). În final, va avea aceleași rezultate ca înainte, dar fără o coloană calculată Profit.

Mai întâi, în tabelul Vânzări, selectam coloana VolumVânzări, apoi faceți clic pe Însumare automată pentru a crea o măsură explicită SumăVânzări. Rețineți: o măsură explicită este una pe care o creăm în zona de calcul a unui tabel din Power Pivot. Facem același lucru pentru coloana COGS. Vom redenumi aceste TOTAL SalesAmount și Total COGS pentru a le face mai ușor de identificat.

Apoi vom crea o altă măsură cu această formulă:

Profit total:=[ VolumVânzare total] - [TOTAL COGS]

După modificarea formatului noii măsuri Profit total în monedă, o putem adăuga la raportul nostru PivotTable.

Puteți vedea noua măsură Profit total returnează aceleași rezultate ca și cum ați crea o coloană calculată Profit, apoi ați plasat-o în VALUES. Diferența este măsura profitului total este mult mai eficientă și face modelul nostru de date mai curat și mai înclinat, deoarece calculăm în momentul respectiv și doar pentru câmpurile pe care le selectăm pentru raportul PivotTable. Până la urmă, nu avem nevoie de acea coloană calculată Profit.

De ce este importantă ultima parte? Coloanele calculate adaugă date la modelul de date, iar datele ocupă memorie. Dacă reîmprospătăm modelul de date, resursele de procesare sunt necesare și pentru a recalcula toate valorile din coloana Profit. Nu trebuie să ne ocupăm de resurse ca aceasta, deoarece dorim într-adevăr să ne calculăm profitul atunci când selectăm câmpurile pentru care dorim profit în raportul PivotTable, cum ar fi categoriile de produse, regiunea sau după date.

Să ne uităm la un alt exemplu. Una în care o coloană calculată creează rezultate care la prima vedere arată corect, dar...

În acest exemplu, dorim să calculăm sumele de vânzări ca procent din totalul vânzărilor. Creăm o coloană calculată denumită % din vânzări în tabelul nostru Vânzări, astfel:

Stările formulei noastre: Pentru fiecare rând din tabelul Vânzări, împărțiți suma din coloana VolumVânzări la totalul SUM al tuturor sumelor din coloana VolumVânzări.

Dacă creăm un raport PivotTable și adăugăm Categorie de produse la COLOANE și selectăm noua coloană % din vânzări pentru a o amplasa în VALORI, obținem un total de % din vânzări pentru fiecare dintre categoriile noastre de produse.

Ok. Arată bine până acum. Dar să adăugăm un slicer. Adăugăm Anul calendaristic, apoi selectați un an. În acest caz, selectăm 2007. Aceasta este ceea ce obținem.

La prima vedere, acest lucru poate apărea în continuare corect. Dar, procentele noastre ar trebui să totalizăm cu adevărat 100%, deoarece dorim să știm procentul total de vânzări pentru fiecare dintre categoriile noastre de produse pentru 2007. Deci, ce nu a mers bine?

Coloana % din vânzări a calculat un procent pentru fiecare rând care este valoarea din coloana VolumVânzare împărțită la suma totală a tuturor valorilor din coloana VolumVânzare. Valorile dintr-o coloană calculată sunt fixe. Acestea sunt un rezultat invariabil pentru fiecare rând din tabel. Când am adăugat % din vânzări la raportul pivotTable, acesta a fost agregat ca sumă a tuturor valorilor din coloana VolumVânzare. Suma tuturor valorilor din coloana % din Vânzări va fi întotdeauna 100%.

Putem șterge coloana calculată % din vânzări, deoarece nu ne va ajuta. În schimb, vom crea o măsură care calculează corect procentul din totalul vânzărilor, indiferent de filtrele sau slicerele aplicate.

Vă amintiți măsura TotalSalesAmount pe care am creat-o anterior, cea care însumează pur și simplu coloana VolumVânzări? L-am utilizat ca argument în măsura Profitul total și îl vom utiliza din nou ca argument în noul câmp calculat.

Vom crea o măsură nouă cu următoarea formulă:

% din total vânzări:=([VolumVânzare total]) / CALCULATE([VolumVânzare total], ALLSELECTED())

Această formulă arată: Împărțiți rezultatul din VolumVânzări total la suma totală a VolumVânzări fără filtre de coloană sau de rând diferite de cele definite în PivotTable.

Acum, dacă adăugăm noul % din totalul vânzărilor în raportul PivotTable, obținem:

Arată mai bine. Acum , procentul nostru din totalul vânzărilor pentru fiecare categorie de produse este calculat ca procent din vânzările totale pentru anul 2007. Dacă selectăm un alt an sau mai mult de un an în slicerul CalendarYear, obținem procente noi pentru categoriile noastre de produse, dar totalul general este încă 100%. Putem adăuga și alte slicere și filtre. Procentul nostru din totalul vânzărilor va produce întotdeauna un procent din vânzările totale, indiferent de slicerele sau filtrele aplicate. Cu măsurile, rezultatul este întotdeauna calculat în funcție de contextul determinat de câmpurile din COLOANE și RÂNDURI și de orice filtre sau slicere aplicate. Aceasta este puterea măsurilor.

Iată câteva indicații care să vă ajute atunci când decideți dacă o coloană calculată sau o măsură este potrivită sau nu pentru o anumită nevoie de calcul:

Utilizarea coloanelor calculate

• Dacă doriți ca datele noi să apară în RÂNDURI, COLOANE sau FILTRE într-un raport PivotTable sau pe O AXĂ, LEGENDĂ sau DALĂ DUPĂ într-o vizualizare Power View, trebuie să utilizați o coloană calculată. La fel ca coloanele obișnuite de date, coloanele calculate pot fi utilizate ca un câmp în orice zonă și, dacă sunt numerice, pot fi agregate și în VALORI.

• Dacă doriți ca datele noi să fie o valoare fixă pentru rând. De exemplu, aveți un tabel de date cu o coloană de date și doriți o altă coloană care conține doar numărul lunii. Puteți crea o coloană calculată care calculează doar numărul lunii din datele din coloana Dată. De exemplu, =MONTH('Dată'[Dată]).

• Dacă doriți să adăugați o valoare text pentru fiecare rând la un tabel, utilizați o coloană calculată. Câmpurile cu valori text nu pot fi agregate niciodată în VALORI. De exemplu, =FORMAT('Dată'[Dată],"mmmm") ne dă numele lunii pentru fiecare dată din coloana Dată din tabelul Dată.

Utilizarea măsurilor

• Dacă rezultatul calculului va fi întotdeauna dependent de celelalte câmpuri pe care le selectați într-un raport PivotTable.

• Dacă trebuie să efectuați calcule mai complexe, cum ar fi să calculați o contorizare pe baza unui filtru de un anumit fel sau să calculați o varianță de la an la an sau de la o varianță, utilizați un câmp calculat.

• Dacă doriți să mențineți dimensiunea minimă a registrului de lucru și să îi maximizați performanța, creați cât mai multe calcule posibil. În multe cazuri, toate calculele dvs. pot fi măsuri, reducând semnificativ dimensiunea registrului de lucru și accelerați timpul de reîmprospătare.

Rețineți că nu este nimic în neregulă cu crearea coloanelor calculate, așa cum am făcut cu coloana Profit, apoi cu agregarea într-un raport Sau PivotTable. De fapt, este o modalitate foarte bună și simplă de a învăța și de a crea propriile calcule. Pe măsură ce înțelegerea acestor două caracteristici extrem de puternice din Power Pivot crește, se recomandă să creați cel mai eficient și mai precis model de date posibil. Să sperăm că ceea ce ați învățat aici vă ajută. Există și alte resurse foarte bune care vă pot ajuta. Iată doar câteva: Context în formulele DAX, agregările din Power Pivot și Centrul de resurse DAX. Și, deși este un pic mai avansat și direcționat către profesioniști în contabilitate și finanțe, modelarea și analiza datelor profit și pierdere cu eșantionul Microsoft Power Pivot din Excel este încărcată cu modele de date și exemple de formule excelente.