Power Pivot (8) – Exemple de functii DAX. Functia CALCULATE (2)
Ince
pand 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 model de date, format din trei tabele, relationate astfel:
Va aduceti aminte din articolele anterioare cum am creat legatura dintre tabele si cum am realizat un Pivot Table de tip Power Pivot in baza unui model de date.
Am construit un raport sintetic Pivot Table de tip Power Pivot, pe asiguratori, care arata astfel:
Vrem sa realizam cateva campuri calculate de utilizator, care sa arate comparativ:
- Rata medie de incasare (valoare dauna/ valoare incasari)
- Valoare medie daune dosare nefinalizate/ numar total dosare
- Numar mediu dosare nefinalizate/ numar total dosare
Starea dosarului este inscrisa in coloana [Status dosar] din tabela dosare si poate avea una dintre urmatoarele valori: finalizat, acte incomplete, in lucru.
- Rata medie de incasare
Este cea mai simplu de calculat masura, am realizat un camp calculat (Calculated Field), pe care l-am denumit chiar rata medie incasare.
In meniul PowerPivot, apasati Calculated Fields si Add new Calculated Field.
Calculati rata medie de incasare dupa formula:
=[Sum of Suma incasare]/sum(dosare[Valoare dauna calculata/de platit (EUR)])
Aceasta inseamna ca va fi calculate in context rata medie de incasare, prin impartirea totalului incasat la totalul daunei calculate).
Gata!
Ce inseamna in context?
Pai sa vedem ce se intampla daca in Pivot Table, inlocuim pe randuri asiguratorul cu regiunea accidentului.
Prin urmare, masura rata medie incasare recalculeaza de fiecare data valorile sintetice, in functie de felul in care este construit Pivot Table (ce avem pe randuri, respectiv coloane).
- Valoare medie daune dosare nefinalizate/ numar total dosare
Sa realizam o formula combinata, in care utilizam functia CALCULATE si functia SUM.
=calculate(sum(dosare[Valoare dauna calculata/de platit (EUR)]),dosare[Status dosar]<>”finalizat”)/sum(dosare[Valoare dauna calculata/de platit (EUR)])
Nu conteaza daca scrieti denumirea functiilor cu majuscule sau nu!
Observati mai intai felul in care am calculat valorile sintetice filtrate, adica totalul daunelor doar pentru dosarele nefinalizate.
Impartim apoi rezultatul functiei CALCULATE la suma valorii daunelor calculate.
Sa vedem rezultatul!
La un calcul simplu, observati ca Grand Total (care reprezinta rata medie totala dosare nefinalizate, adica valoare totala daune dosare nefinalizate/valoare totala daune dosare) nu corespunde cu suma valorilor din coloana respectiva.
In fapt, campul calculat anterior realizeaza in context sinteza, adica imparte valoarea totala a daunelor pentru dosarele nefinalizate aferente fiecarui rand din sinteza (in cazul de mai sus, regiuni) la valoarea totala a daunelor pentru toate dosarele aferente fiecarui rand din sinteza.
Cerinta exercitiului era insa ca impartirea sa se faca la valoarea totala a daunelor din tabela dosare, indiferent de context (adica de modul de sinteza a datelor).
Pentru a exprima corect acest calcul, realizam un alt camp calculat, denumit %dosare nefinalizate (valoare % din total general).
Vom inscrie o formula usor diferita de cea anterioara, la numitor:
=CALCULATE(SUM(dosare[Valoare dauna calculata/de platit (EUR)]),dosare[Status dosar]<>”finalizat”)/CALCULATE(SUM(dosare[Valoare dauna calculata/de platit (EUR)]),all(‘dosare’))
Prin urmare,
sum(dosare[Valoare dauna calculata/de platit (EUR)])
a devenit
CALCULATE(SUM(dosare[Valoare dauna calculata/de platit (EUR)]),all(‘dosare’))
, unde all(‘dosare’) – atentie la sintaxa! – reprezinta argumentul Filter1 al functiei CALCULATE.
Aceasta formula va calcula totalul general, pentru toate articolele din tabela dosare.
Tabelul sintetic de tip Pivot Table va arata acum astfel:
- Numar mediu dosare nefinalizate/ numar total dosare
Vrem acum sa calculam o rata procentuala care sa arate cat la suta reprezinta in context dosarele nefinalizate din total de dosare, de data aceasta ca numar de dosare, nu ca valoare a daunelor.
Vom modifica putin formula anterioara, utilizand functia COUNT in loc de functia SUM.
Am denumit aceasta masura calculata de utilizator rata dosare nefinalizate.
=CALCULATE(COUNT(dosare[Valoare dauna calculata/de platit (EUR)]),dosare[Status dosar]<>”finalizat”)/CALCULATE(COUNT(dosare[Valoare dauna calculata/de platit (EUR)]),all(‘dosare’))
Tabelul sintetic final (am mutat pe ROWS asiguratorii si am renuntat la campul Regiune accident din Pivot Table) va arata astfel:
Pentru o mai buna vizibilitate, am realizat o formatare conditionata simpla pentru a evidentia top 3 ccele mai mari rate ale dosarelor nefinalizate ca % din totalul daunelor calculate.
Iata cum putem utiliza functia CALCULATE in combinatii complexe, pentru a realiza masuri (indicatori) sintetice relevante pentru analiza.
