Power Pivot (4) – Cum arata si ce optiuni furnizeaza Pivot Table utilizand un model de date?
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! 🙂
Avem un model de date format din trei tabele (dosare, incasari, regiuni), relationate astfel:
Utilizand optiunile clasice furnizate de Excel, putem realiza o reprezentare sintetica a celor trei tabele, utilizand optiunea PivotTable and PivotChart Wizard – Multiple Consolidation Ranges.
Aceasta optiune realizeaza un fel de consolidare a datelor si are cam aceeasi utilitate pentru exemplul nostru ca si optiunea Consolidation din meniul Data.
Sa presupunem ca vrem sa realizam un raport sintetic in care sa reprezentam valoarea totala a incasarilor pe regiuni.
Incasarile apar in tabela incasari, acolo unde sunt inregistrate cronologic toate incasarile aferente dosarelor (adica putem avea o incasare, mai multe incasari, nicio incasare pentru un dosar).
Apasati butonul Manage din meniul PowerPivot, pentru a accesa optiunile specifice Power BI.
Modelul de date arata astfel (am utilizat reprezentarea Diagram View):
Apasati butonul PivotTable din meniul Home al Modelului de date si alegeti din fereastra de dialog (ca la PivotTable clasic) unde vreti sa pozitionati raportul. Am ales sa apara intr-o noua foaie de lucru.
Apasati OK.
Observati ca foaia de lucru apare in fisierul activ si modalitatea de realizare a raportului de tip Pivot Table seamana cu optiunile clasice furnizate de Excel, cu exceptia faptului ca, in partea dreapta a ecranului (PivotTable Fields) apar toate cele trei tabele relationate din model.
– Microsoft Excel 2013
Vrem sa realizam o sinteza a incasarilor pe regiuni. Aduceti in zona Rows coloana Regiune (din tabela regiuni). In zona de date (Values), aduceti coloana Suma incasare din tabela incasari.
Gata!
Utilizand modelul de date, am reusit din cateva click-uri sa realizam o sinteza rapida a incasarilor pe regiuni, utilizand informatiile din tabelele regiuni, incasari si dosare.
Veti intreba: dar unde am utilizat in acest PivotTable tabela dosare?
Daca tabela incasari nu ar fi relationata cu tabela dosare prin coloana Indicativ dosar, nu ar fi posibil calculul sintetic pe regiuni.
Tabela regiuni contine doar doua coloane: Tara accident si regiunea corespondenta (coloana Regiune).
Tabela incasari nu contine decat trei coloane: Indicativ dosar, Data incasare, Suma incasare.
Tabela dosare contine informatii detaliate despre dosare si contine, printre altele, coloanele: Tara accident si Indicativ dosar.
Atunci cand am realizat modelul de date, am relationat tabelele astfel incat:
- Unei tari ii corespunde o singura regiune.
- Unui indicativ dosar ii corespunde o singura tara.
Pentru a putea realiza sinteza de mai sus, a fost interogata mai intai tabela dosare, de unde s-a extras Tara accidentului, pentru a cauta in tabela relationata regiuni, regiunea corespondenta.
Pentru a calcula totalul incasarilor pe regiuni, pentru fiecare Indicativ dosar din tabela incasari, a fost cautata valoarea corespunzatoare indicativului de dosar din tabela dosare, de pe coloana Tara accidentului.
Sinteza dupa data accidentului si utilizarea optiunii Insert Timeline
Sa incercam o altfel de reprezentare sintetica a datelor. Vrem sa vizualizam rapid incasarile aferente accidentelor, sintetizate in functie de trimestrul si anul accidentului.
Realizam un Pivot Table, utilizand modelul de date deja existent, unde pe randuri apare Data Accident, si sinteza (Values) se face atat dupa Suma incasata (coloana din tabela incasari), cat si dupa valoarea daunei calculate/de platit (coloana din tabela dosare), astfel incat sa putem vizualiza comparativ incasarile versus targetul de incasat (adica valoarea daunei care trebuie incasata ulterior).
Pentru o mai buna lizibilitate, am denumit coloanele sintetice Total incasat, respectiv Total dauna.
Pivot Table va arata astfel:
Desigur, exista posibilitatea ca intr-o zi sa fi avut loc mai multe accidente, prin urmare valorile sintetice reprezinta valoarea totala a daunei calculate (in baza coloanei Valoare dauna calculata/de platit din tabela dosare), respectiv valoarea totala a incasarilor (Suma incasata) aferenta accidentului/accidentelor din ziua respectiva.
Cum s-a facut analiza Power BI in baza modelului construit?
Sinteza incasarilor s-a facut in functie de data incasarii sau in functie de Data accidentului? Si daca s-a facut in functie de Data accidentului, cum s-a facut calculul?
Pai, fiecare articol din tabela incasari are trei valori, pentru fiecare dintre cele trei caracteristici: Indicativ dosar, Data incasare, Suma incasata.
Pe randuri (in Pivot Table) am pus Data accidentului. Dar fiecare articol din tabela dosare, are pe langa valoarea Data accident si un Indicativ dosar.
Tabelele dosare si incasari sunt relationate in modelul de date exact prin coloana Indicativ dosar.
Deci va cauta indicativul (sau indicativele) de dosar aferente datei accidentului in tabela incasari, unde va calcula totalul incasarilor aferente acelor/ acelui indicativ(e) de dosar.
Putem aplica acum si un filtru de tip Timeline, astfel incat sa vizualizam numai acele date de care avem nevoie.
Apasati butonul Insert Timeline din submeniul Analyze al meniului dinamic PivotTable Tools si bifati Data accident.
Hai sa vizualizam comparativ acccidentele petrecute doar in 2014 si 2015. Observati ca am bifat optiunea YEARS, pentru a selecta mai usor anii.
Utilizati click&drag (tineti click stanga apasat si “trageti” pur si simplu de marginea benzii albastre pentru a redimensiona perioada de timp dorita).
Daca doream sa alegem anumite trimestre, bifam optiunea Quarters:
Acest lucru nu inseamna ca s-a modificat si nivelul de sinteza in PivotTable, adica data accidentului va aparea tot detaliat, nu ca atunci cand am fi utilizand optiunea Group Selection. Aceasta optiune nici nu este activa atunci cand realizam un Pivot Table ce are ca sursa un model de date.
Optiuni suplimentare furnizate de Power Pivot
- Adaugarea de noi tabele/ conexiuni la modelul de date
Puteti adauga oricand la un raport de tip Pivot Table o noua tabela, definind obligatoriu si cel putin un tip de relatie cu cel putin unul din tabelele deja existente in Data Model.
Apasati butonul Change Data Source si alegeti optiunea Connection Properties. In fereastra de dialog apasati butonul Add si alegeti optiunea Add to Data Model..
Aduceti-va aminte cu definiti conexiuni intre tabele si realizati un model de date, din articolele anterioare dedicate Power Pivot pentru Excel 2013.
- Modificarea conexiunilor existente intre tabele
- Realizarea de masuri (Calculated fields), utilizand functii specifice DAX
- Reprezentarea grafica a KPIs (indicatori de performanta)
Dar despre acestea in articolele urmatoare.
