Power Pivot (7) – Exemple de Functii DAX. Functia CALCULATE
Incepand din 11 iulie 2016, vom publica in fiecare zi de luni si de joi cate un articol din seria de zece cursuri gratuite dedicate Power Pivot pentru Excel 2013.
ENJOY THE POWER! 🙂
Se da un Pivot Table de tip Power Pivot, construit in baza unui model de date format din mai multe tabele relationate astfel:
In Pivot Table, vrem sa introducem un camp calculat (Calculated field) care sa calculeze sintetic rata de incasare. Cu alte cuvinte, vrem sa calculam o asa-numita masura, care sa calculeze un indice de tipul: total valoare facturi platite/total valoare facturi.
Deocamdata, construim un pivot simplu, care contine urmatoarele campuri:
- ROWS – Activity type (coloana din tabela contracte!)
- COLUMNS – Status factura (coloana calculata din tabela facturi), in baza relatiei definite intre tabela facturi si tabela FACT_RESTANTE
- VALUES –Invoice value[EUR] (coloana calculata din tabela facturi), in baza relatiei definite intre tabela facturi si tabela curs_lunar
Coloana Status factura (vezi aici cum am realizat aceasta coloana calculata) contine una dintre valorile: platit, neplatit.
Ceea ce vrem noi sa calculam, inseamna practic sa impartim totalul aferent statusului platit la totalul general pe rand (Grand Total).
Mai mult decat atat, am vrea sa realizam un Pivot Table de tip Power Pivot care sa arate pe coloane si anii de facturare, astfel incat, pe fiecare activitate (sau alt tip de sinteza, pe clienti, de exemplu), sa vedem defalcat rata de incasare, pe fiecare an de facturare.
Vom utiliza functia CALCULATE, o functie DAX de tip Filter, o categorie speciala de functii care nu se regaseste in setul de functii disponibile in varianta clasica de Excel.
Sintaxa functiei CALCULATE este:
=CALCULATE(Expression, [Filter1],[Filter2],…), unde
Expression – expresia care trebuie evaluata (calculata)
Filter1,filter2,… – argument optional; expresii de tip Boolean sau de tip tabel, care definesc un filtru.
Functia CALCULATE nu poate fi utilizata in cadrul unui model de date decat sub forma unei expresii sintetice de tip Calculated Field. Cu alte cuvinte, spre deosebire de functia RELATED sau functia SWITCH, nu poate fi utilizata pentru a introduce in tabelele relationate din model noi coloana calculate.
Campurile calculate realizate in cadrul Power BI apar doar in Pivot Table si, spre deosebire de campurile calculate disponibile in Pivot Table clasic, functiile utilizate la calcularea lor pot utiliza ca argumente coloane (sau alte campuri calculate) din oricare din tabelele relationate din modelul de date.
Pentru a insera un camp calculat de utilizator, apasati butonul Calculated Field din meniul PowerPivot si alegeti optiunea New Calculated Field.
Daca vreti sa modificati un camp calculat anterior, alegeti optiunea Manage Calculated Fields.
Va aparea urmatoarea fereastra de dialog:
Modificati denumirea predefinita Calculated field 1 ….., pentru a putea regasi mai usor campurile calculate realizate. Am dat denumirea %platit noului camp calculat de utilizator.
Introducem deocamdata in rubrica urmatoarea formula:
=CALCULATE([Sum of Invoice valueEUR],facturi[Status factura]=”platit”)
CALCULATE reprezinta un fel de super-SUMIF, care realizeaza calcule sintetice pentru Pivot Tables de tip Power Pivot.
Formula de mai sus realizeaza o sinteza (recalculata automat in functie de coloana pe care o alegem in rubrica ROWS a Pivot Table) a valorii facturilor in euro, tinand cont de urmatoarea conditie: statusul facturii sa fie textul “platit”. Cu alte cuvinte, formula de mai sus va realiza un calcul sintetic al sumei totale a facturilor platite.
Pentru a verifica corectitudinea formulei, apasati butonul Check formula.
Formatati rezultatul. In exemplul de mai sus, am ales o reprezentare cu 2 zecimale si fara separator de mii.
Deocamdata am realizat calculul valorii totale a facturilor platite.
Pentru a obtine rezultatul de care aveam nevoie, formula anterioara trebuie completata prin impartirea la suma totala a facturilor.
Formula finala va fi:
=CALCULATE([Sum of Invoice valueEUR],facturi[Status factura]=”platit”)/[Sum of Invoice valueEUR]
Alternativ, puteam utiliza si urmatoarea formula:
=CALCULATE(sum([Invoice value[EUR]]]),facturi[Status factura]=”platit”)/[Sum of Invoice valueEUR], in care argumentul Expression este rezultatul functiei SUM aplicat pentru coloana calculata [Invoice value[EUR]]].
Gata!
In Pivot Table, introduceti si coloana YearID pe coloane.
Raportul sintetic va arata acum astfel:
In sinteza de mai sus, pentru categoria Inspectii, de exemplu, rezultatul se citeste astfel:
- Nu au fost facturari in anii 2013 si 2014
- In anul 2015, s-a incasat 98.65% din valoarea totala a facturilor emise in 2015 pentru categoria Inspectii
- In anul 2016, s-a incasat 87.45% din valoarea totala a facturilor emise in 2016 pentru categoria Inspectii
- Valoarea totala a incasarilor pentru categoria Inspectii, pentru intreaga perioada de analiza, este de 97.26 din totalul facturarilor pe aceasta categorie
In articolul urmator, vom exemplifica si alte modalitati de utilizare a functiei CALCULATE.
