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

Când învățați pentru prima dată cum să utilizați Power Pivot, majoritatea utilizatorilor descoperă că puterea reală este în agregare sau în calculul unui rezultat într-un anumit fel. Dacă datele dvs. au o coloană cu valori numerice, puteți să o agregți selectând-o într-un raport PivotTable sau într-o Listă de câmpuri Power View. Prin natura sa, deoarece este numerică, va fi însumată automat, va face media, va fi luată în calcul sau orice tip de agregare selectați. Aceasta se mai știe ca măsură implicită. Măsurile implicite sunt foarte bune pentru agregare rapidă și simplă, dar au limite, iar aceste limite pot fi aproape întotdeauna depășite cu măsuri explicite și coloane calculate.

Să vedem mai întâi un exemplu în care utilizăm o coloană calculată pentru a adăuga o valoare text nouă pentru fiecare rând dintr-un tabel numit 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ț de cas, etc.. Avem un alt tabel asociat, denumit Categorie produs, care conține o coloană ProductCategoryName. Ceea ce dorim este 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 de produse, astfel:

Noua noastră formulă Categorie produs utilizează funcția RELATED DAX pentru a obține valori din coloana ProductCategoryName din tabelul Categorie produs asociat, apoi introduce acele valori pentru fiecare produs (fiecare rând) din tabelul Produs.

Acesta este un exemplu excelent 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 marja de profit pentru categoriile noastre de produse. Acesta este un scenariu comun, 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 volume de vânzări și o altă coloană care are costuri.

Putem crea o coloană calculată care calculează o valoare de profit pentru fiecare rând prin scăderea valorilor din coloana COGS din valorile din coloana CantitateVând, astfel:

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

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

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

Ceea ce am făcut este să creăm 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 nostru PivotTable, creând automat o măsură implicită, unde un rezultat este calculat 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, 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 în care a fost agregat pentru fiecare dintre categoriile de produse. Dacă vă gândiți, de asemenea, că nu trebuie să creăm coloana calculată Profit, sunteți, de asemenea, corectă. Dar cum putem calcula apoi profitul fără a crea o coloană calculată profit?

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

Momentan, vom lăsa coloana Profit calculată în tabelul Vânzări și Categoria produselor în COLOANE și Profitul în valorile noastre PivotTable pentru a compara rezultatele.

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

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

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

Profit total:=[ Valoare totală vânzări] - [TOTAL COGS]

După ce schimbați formatul noii noastre măsuri Profit total în monedă, îl putem adăuga în raportul PivotTable.

Puteți vedea noua măsură Profit total a noastră returnează aceleași rezultate ca și crearea unei coloane calculate Profit și plasarea ei în VALUES. Diferența este măsura noastră Profit total este mult mai eficientă și face modelul nostru de date mai clar și mai înclinat, deoarece calculăm la un moment dat doar pentru câmpurile pe care le selectăm pentru raportul nostru PivotTable. Până la urmă nu avem nevoie de acea coloană calculată Profit.

De ce este această ultimă parte importantă? Coloanele calculate adaugă date la modelul de date, iar datele preiau 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 chiar necesar să preiam 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ă vedem un alt exemplu. Una în care o coloană calculată creează rezultate care arată corect la prima vedere, dar....

În acest exemplu, dorim să calculăm vânzările ca procent din totalul vânzărilor. Vom crea o coloană calculată numită % din vânzări în tabelul Vânzări, astfel:

Stările formulei noastre: Pentru fiecare rând din tabelul Vânzări, împărțiți cantitatea din coloana Cantitate Vânzări la totalul sumei pentru toate volumele din coloana Cantitate Vânzări.

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

ok. Totul arată bine până acum. Dar să adăugăm un slicer. Adăugăm Anul calendaristic și apoi selectăm un an. În acest caz, selectăm 2007. De aici obținem.

La prima vedere, este posibil ca aceasta să pară corectă. Dar procentele noastre ar trebui, într-adevăr, să fie de 100%, întrucât dorim să știm ce procent din vânzările totale pentru fiecare dintre categoriile noastre de produse pentru anul 2007. Ce nu a mers bine?

Coloana noastră % din vânzări a calculat un procent pentru fiecare rând care este valoarea din coloana Valoare SalesAmount împărțită la suma totală a tuturor valorilor din coloana Valoare SalesAmount. Valorile dintr-o coloană calculată sunt fixe. Acestea sunt un rezultat insubil pentru fiecare rând din tabel. Când am adăugat % din vânzări în raportul nostru PivotTable, acesta a fost agregat ca o sumă a tuturor valorilor din coloana Valoare SalesAmount. Această sumă a tuturor valorilor din coloana % vânzări va fi întotdeauna 100%.

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

Rețineți măsura TotalSalesAmount creată anterior, cea care însumează pur și simplu coloana Valoare SalesAmount? L-am utilizat ca argument în măsura Profit total și o vom utiliza din nou ca argument în noul câmp calculat.

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

% din totalul vânzărilor:=([Valoare totală Vânzări]) / CALCULATE([Valoare totală Vânzări], ALLSELECTED())

Această formulă spune: Împărțiți rezultatul din Total VânzăriAmount la suma totală Valoare SalesAmount, fără filtre de coloană sau rând decât cele definite în raportul PivotTable.

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

Arată mai bine. Acum, procentul 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 mult de un an în slicerul AnCal calendar, obținem procente noi pentru categoriile de produse, dar totalul nostru general este tot de 100%. Putem adăuga și alte slicere și filtre. Măsura noastră % din total vânzări va produce întotdeauna un procent din totalul vânzărilor, indiferent de slicerele sau filtrele aplicate. Cu măsuri, rezultatul este calculat întotdeauna în funcție de contextul determinat de câmpurile din COLOANE și RÂNDURI și de filtrele sau slicerele care se aplică. Aceasta este puterea măsurilor.

Iată câteva instrucțiuni care vă ajută atunci când decideți dacă o coloană calculată sau o măsură este potrivită 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 și 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ă numai 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 niciodată agregate în VALUES. De exemplu, =FORMAT('Dată'[Dată],"mmmm") ne oferă 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 selectate într-un raport PivotTable.

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

• Dacă doriți să mențineți dimensiunea registrului de lucru la un minim ș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 accelerează timpul de reîmprospătare.

Rețineți, nu este nimic în neregulă cu crearea de coloane calculate, așa cum am făcut cu coloana Profit și agregarea ei într-un raport sau raport PivotTable. De fapt este o modalitate foarte bună și simplă de a învăța și de a crea calcule proprii. Pe măsură ce înțelegerea acestor două caracteristici extrem de puternice ale Power Pivot crește, se va dori să creați cel mai eficient și mai precis model de date pe care îl puteți face. Sperăm că ne ajută ceea ce ați învățat aici. Există și alte resurse foarte bune care vă pot ajuta. Iată doar câteva: Contextul în formulele DAX,agregărileîn Power Pivot și Centrul de resurse DAX. Iar dacă este puțin mai avansat și orientat către specialiștii în contabilitate și finanțe, Modelarea și analiza datelor despre profit și pierderi cu Microsoft Power Pivot într-un eșantion Excel este încărcat cu modele de date și exemple de formule excelente.