Când învață pentru prima dată cum să utilizeze Power Pivot, majoritatea utilizatorilor descoperă că puterea reală constă în agregarea sau calcularea unui rezultat într-un fel. Dacă datele dvs. 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 lor, deoarece este numeric, acesta va fi automat adunat, mediat, numărat sau orice tip de agregare pe care îl selectați. Aceasta se numește 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ăsuri explicite și coloane calculate.
Haideți să examinăm 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, numit Produs. Fiecare rând din tabelul Produse conține tot felul de informații despre fiecare produs pe care îl vindem. Avem coloane pentru numele produsului, culoare, mărime, preț dealer etc. Avem un alt tabel asociat denumit Categorie produs, care conține o coloană NumeCategorieProdus. Dorim ca fiecare produs din tabelul Produs să includă numele categoriei de produse din tabelul Categorie de produse. În tabelul Produse, putem crea o coloană calculată denumită Categorie de produse, astfel:
Noua noastră formulă Categorie de produs utilizează funcția DAX RELATED pentru a obține valori din coloana NumeCategorieProdus din tabelul Categorie de produs asociat, apoi introduce acele valori pentru fiecare produs (fiecare rând) din tabelul Produse.
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 o putem utiliza ulterior în zona RÂNDURI, COLOANE sau FILTRE din PivotTable sau într-un raport Power View.
Să creăm 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 în multe tutoriale. Avem un tabel Vânzări în modelul nostru de date care conține date despre tranzacții ș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 sume de vânzări și o altă coloană care conține costuri.
Putem crea o coloană calculată care calculează o valoare de profit pentru fiecare rând scăzând valorile din coloana COGS din valorile din coloana CantitateVânzări, astfel:
Acum, putem să creăm un raport PivotTable și să glisăm câmpul Categorie produs în COLOANE și 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ă de profit. Este o cantitate agregată de valori din coloana de profit pentru fiecare dintre categoriile diferite de produse. Rezultatul nostru arată astfel:
În acest caz, Profit are sens doar ca câmp din VALORI. Dacă am pune Profit în zona COLOANE, raportul nostru PivotTable ar arăta astfel:
Câmpul nostru Profit nu furnizează 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ă numită 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 cu adevărat profitul pentru categoriile noastre de produse de două ori, aveți dreptate. 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 trebuie să creăm coloana Profit calculat, aveți dreptate. Dar cum ne calculăm profitul fără a crea o coloană calculată de Profit?
Profitul ar fi într-adevăr mai bine calculat ca măsură explicită.
Pentru moment, vom lăsa coloana Profit calculat în tabelul Vânzări și Categoria de produse în COLOANE și Profit în VALORI din raportul 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 cele din urmă, va da aceleași rezultate ca și înainte, dar fără o coloană calculată Profit.
Mai întâi, în tabelul Vânzări, selectăm coloana CantitateVânzări, apoi faceți clic pe Însumare automată pentru a crea o măsură explicită Sumă de CantitateVâ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 COGS Total SalesAmount și Total COGS pentru a le face mai ușor de identificat.
Apoi creăm altă măsură cu această formulă:
Profit total:=[VolumVânzăriTotal] - [Cogs total]
Notă
De asemenea, am putea scrie formula ca Profit total:=SUM([CantitateVânzări]) - SUM([COGS]), dar prin crearea de măsuri separate VolumVânzări totale și COGS total, le putem utiliza și în raportul nostru PivotTable și le putem folosi ca argumente în tot felul de alte formule cu măsuri.
După ce schimbați noul format al măsurii Profitului total în valută, o putem adăuga în raportul nostru PivotTable.
Puteți vedea că noua noastră măsură Profit total returnează aceleași rezultate ca atunci când creați o coloană calculată Profit și o plasați apoi în VALORI. Diferența este că măsurarea Profitului total este mult mai eficientă și face modelul nostru de date mai curat și mai suplu, deoarece calculăm la momentul respectiv și numai pentru câmpurile pe care le selectăm pentru raportul PivotTable. Până la urmă, nu prea avem nevoie de acea coloană Profit calculat.
De ce este importantă această ultimă parte? Coloanele calculate adaugă date la modelul de date, iar datele ocupă memorie. Dacă reîmprospătăm modelul de date, sunt necesare și resurse de procesare pentru a recalcula toate valorile din coloana Profit. Nu este nevoie să ocupăm resurse de genul acesta, deoarece vrem cu adevărat să ne calculăm profitul atunci când selectăm câmpurile pentru care dorim Profit în raportul PivotTable, cum ar fi categorii de produse, regiune sau după date.
Să ne uităm la alt exemplu. Una în care o coloană calculată creează rezultate care, la prima vedere, par corecte, dar...
În acest exemplu, dorim să calculăm volumul vânzărilor ca procent din totalul vânzărilor. Creăm o coloană calculată denumită % din vânzări în tabelul Vânzări, astfel:
Formula noastră spune: Pentru fiecare rând din tabelul Vânzări, împărțiți cantitatea din coloana CantitateVânzări la totalul SUM al tuturor sumelor din coloana CantitateVânzări.
Dacă creăm un raport PivotTable, adăugăm Categorie de produse la COLOANE și selectăm noua coloană % din vânzări pentru a o adăuga în VALORI, obținem o sumă totală de % din vânzări pentru fiecare dintre categoriile noastre de produse.
Bine. Acest lucru arată bine până acum. Dar să adăugăm un slicer. Adăugăm Calendar Year și apoi selectăm un an. În acest caz, vom selecta 2007. Asta obținem.
La prima vedere, acest lucru ar putea părea totuși corect. Dar procentele noastre ar trebui să totalizeze 100%, pentru că vrem să știm procentul din totalul vânzărilor pentru fiecare dintre categoriile noastre de produse pentru 2007. Deci, ce nu a funcționat?
Coloana noastră % din vânzări a calculat un procent pentru fiecare rând care este valoarea din coloana CantitateVânzări împărțit la suma totală a tuturor valorilor din coloana CantitateVânzări. Valorile dintr-o coloană calculată sunt fixe. Acestea sunt un rezultat invariabil pentru fiecare rând din tabel. Când am adăugat procentaj din vânzări la raportul nostru PivotTable, acesta a fost agregat ca o sumă a tuturor valorilor din coloana CantitateVânzări. Suma tuturor valorilor din coloana % din vânzări va fi întotdeauna 100%.
Sfat
Asigurați-vă că citiți Contextul în formulele DAX. Aceasta oferă o bună înțelegere a contextului la nivel de rând și a contextului de filtrare, pe care este ceea ce descriem aici.
Putem șterge coloana noastră calculată pentru procentul vânzărilor, deoarece nu ne va ajuta. În schimb, vom crea o măsură care calculează corect procentul nostru din totalul vânzărilor, indiferent de filtrele sau slicerele aplicate.
Vă amintiți măsura TotalVânzăriValoare pe care am creat-o mai devreme, cea care însumează pur și simplu coloana CantitateVânzări? L-am utilizat ca argument în măsurarea Profitului total și îl vom folosi din nou ca argument în noul nostru câmp calculat.
Sfat
Crearea de măsuri explicite, cum ar fi VolumVânzări totale și COGS total, nu sunt utile doar într-un raport PivotTable sau într-un raport, ci sunt utile și ca argumente în alte măsuri atunci când aveți nevoie de rezultat ca argument. Acest lucru face formulele mai eficiente și mai ușor de citit. Aceasta este o bună practică de modelare a datelor.
Creăm o măsură nouă cu următoarea formulă:
% din totalul vânzărilor:=([TotalVolumVânzări]) / CALCULATE([TotalVolumVânzări], ALLSELECTED())
Această formulă spune: Împărțiți rezultatul din TotalVolumVânzări la suma totală a VolumVânzări, fără filtre de coloană sau rând, altele decât cele definite în raportul PivotTable.
Sfat
Nu uitați să citiți despre funcțiile CALCULATE și ALLSELECTED în referința DAX.
Acum, dacă adăugăm noul nostru procent din totalul vânzărilor la raportul PivotTable, obținem:
Pare mai bine. Acum, procentul nostru din totalul vânzărilor pentru fiecare categorie de produse este calculat ca procent din totalul vânzărilor pentru anul 2007. Dacă selectăm un alt an sau mai mulți ani în slicerul Anul calendaristic, primim procente noi pentru categoriile noastre de produse, dar totalul general este în continuare de 100%. Putem adăuga și alte slicere și filtre. Măsura noastră % din totalul vânzărilor va produce întotdeauna un procent din totalul vânzărilor, indiferent de orice slicere sau filtre aplicate. În cazul măsurătorilor, rezultatul este întotdeauna calculat în funcție de contextul determinat de câmpurile din COLOANE și RÂNDURI și de orice filtre sau slicere care se aplică. Aceasta este puterea măsurătorilor.
Iată câteva sfaturi care vă pot ajuta 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 noile date să apară în RÂNDURI, COLOANE sau în FILTRE într-un raport PivotTable sau pe o AXĂ, pe o LEGENDĂ sau pe o ÎNCADRARE 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ă, iar dacă sunt numerice pot fi agregate, de asemenea, î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 pornind de la datele din coloana Dată. De exemplu, =MONTH('Dată'[Dată]).
- Dacă doriți să adăugați o valoare text pentru fiecare rând al unui 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ă.
Măsuri de utilizare
- Dacă rezultatul calculului va depinde întotdeauna de celelalte câmpuri pe care le selectați într-un raport PivotTable.
- Dacă trebuie să efectuați calcule mai complexe, cum ar fi calcularea unei contorizări pe baza unui filtru sau calcularea unei varianțe de la an la an sau de varianță, utilizați un câmp calculat.
- Dacă doriți să mențineți dimensiunea registrului de lucru la minimum și să îi maximizați performanța, creați cât mai multe măsurători pentru calcule. În multe cazuri, toate calculele dvs. pot fi măsuri, reducând semnificativ dimensiunea registrului de lucru și accelerând 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, și apoi agregarea lor într-un raport PivotTable. De fapt, este un mod foarte bun și ușor de a învăța și a crea propriile calcule. Pe măsură ce înțelegerea acestor două caracteristici extrem de puternice ale Power Pivot crește, se recomandă să creați cel mai eficient și mai precis model de date posibil. Sperăm că ceea ce ați învățat aici vă ajută. Există și alte resurse grozave care vă pot ajuta și pe dvs. Iată doar câteva exemple: Contextul în formulele DAX, agregările în Power Pivot și Centrul de resurse DAX. Și, deși este puțin mai avansat și direcționat către profesioniștii din contabilitate și finanțe, eșantionul Profit and Loss Data Modeling and Analysis cu Microsoft Power Pivot în Excel include numeroase exemple de modelare de date și formule.