Laskettujen sarakkeiden ja laskettujen kenttien käyttäminen

Useimmat käyttäjät huomaavat Power Pivotin käyttöä ensi kertaa opetellessaan, että sen todellinen voima on tuloksen koostamisessa tai laskennassa. Jos tiedoissa on sarake, jossa on numeerisia arvoja, voit helposti koostaa sen valitsemalla Pivot-taulukon tai Power View -kenttäluettelon. Koska se on luonteeltaan numeerinen, se koostetaan automaattisesti summaamalla, laskemalla keskiarvo tai laskemalla lukumäärä valitun tyypin mukaan. Tätä kutsutaan implisiittiseksi mittayksiköksi. Implisiittisten mittayksiköiden avulla koostaminen on nopeaa ja helppoa, mutta niillä on rajoituksensa, jotka voidaan lähes aina välttää eksplisiittisten mittayksiköiden ja laskettujen sarakkeiden avulla.

Tarkastellaan ensin esimerkkiä, jossa Tuote-nimisen taulukon jokaiselle riville lisätään uusi tekstiarvo lasketun sarakkeen avulla. Tuote-taulukon jokaisella rivillä on kaikenlaisia tietoja jokaisesta myymästämme tuotteesta. Sarakkeiden otsikoita voivat olla esimerkiksi Tuotteen nimi, Väri, Koko ja Jälleenmyyntihinta. Meillä on myös toinen taulukko nimeltä Tuoteluokka, joka sisältää TuoteluokanNimi-sarakkeen. Haluamme, että Tuote-taulukon jokaisella tuotteella on Tuoteluokka-taulukossa mainittu tuoteluokan nimi. Voimme luoda Tuote-taulukkoon tällaisen lasketun Tuoteluokka-sarakkeen:

Uusi Tuoteluokka-kaavamme käyttää RELATED DAX -funktiota arvojen määritykseen liittyvän Tuoteluokka-taulukon ProductCategoryName-sarakkeesta ja lisää sitten kyseiset arvot kullekin Tuote-taulukon tuotteelle (kullekin riville).

Tämä on hyvä esimerkki siitä, miten voit lasketun sarakkeen avulla lisätä jokaiselle riville kiinteän arvon, jota voimme myöhemmin käyttää Pivot-taulukon tai Power View -raportin RIVIT-, SARAKKEET- tai SUODATTIMET-alueella.

Luodaan vielä toinen esimerkki, jossa haluamme laskea tuoteluokkiemme katetuoton. Tämä on yleinen skenaario monissa opetusohjelmissakin. Tietomallissamme on Myynti-taulukko, joka sisältää tapahtumatiedot, ja Myynti-taulukon ja Tuoteluokka-taulukon välillä on suhde. Myynti-taulukossa on sarake, jossa ovat myyntisummat, ja toinen sarake, jossa ovat kustannukset.

Voimme luoda lasketun sarakkeen, joka laskee jokaisen rivin tuottosumman vähentämällä Myytyjen tuotteiden kustannukset -sarakkeen arvot Myyntisumma-sarakkeen arvoista seuraavasti:

Nyt voimme luoda Pivot-taulukon ja vetää Tuoteluokka-kentän SARAKKEET-alueelle ja uuden Tuotto-kentän ARVOT-alueelle (PowerPivot-taulukon sarake on Pivot-taulukon kenttäluettelon kenttä). Tuloksena on implisiittinen mittayksikkö Tuottosumma. Se on eri tuoteluokkien tuottosarakkeiden arvojen summa. Tuloksemme näyttää tältä:

Tässä tapauksessa Tuotto-kenttä on hyödyllinen vain ARVOT-alueen kenttänä. Jos sijoittaisimme Tuotto-kentän SARAKEET-alueelle, Pivot-taulukkomme näyttäisi tältä:

Tuotto-kenttämme ei sisällä mitään hyödyllisiä tietoa, jos se sijoitetaan SARAKKEET-, RIVIT- tai SUODATTIMET-alueelle. Se on hyödyllinen vain ARVOT-alueen koostettuna kenttänä.

Olemme luoneet Tuotto-nimisen sarakkeen, joka laskee myyntitaulukon kullekin riville katemarginaalin. Lisäsimme sitten Pivot-taulukon ARVOT-alueelle Tuotto-kentän, joka luo automaattisesti implisiittisen mittayksikön, jossa tulos lasketaan kullekin tuoteluokille. Jos uskot, että tuoteluokkien tuotto on todella laskettu kahdesti, olet oikeassa. Laskemme ensin tuottoa kullekin Myynti-taulukon riville ja lisäsimme sitten Tuotto-kentän ARVOT-alueelle, jossa se koostettiin kullekin tuoteluokille. Jos uskot myös, että laskettua Tuotto-saraketta ei olisi tarpeen luoda, olet myös oikeassa. Mutta miten sitten laskemme tuoton luomatta laskettua Tuotto-saraketta?

Tuotto olisi parasta laskea eksplisiittisenä mittayksikkönä.

Toistaiseksi jätämme lasketun Tuotto-sarakkeemme Myynti-taulukkoon, Tuoteluokka-kentän SARAKKEET-alueelle ja Tuotto-kentän Pivot-taulukkoon, jotta voimme vertailla tuloksia.

Luomme Myynti-taulukkomme laskenta-alueelle mittayksikön nimeltä Yhteenlaskettu tuotto (nimeämisristiriitojen välttämiseksi). Se antaa lopulta saman tuloksen kuin aiemminkin saimme, mutta ilman laskettua Tuotto-saraketta.

Valitsemme ensin Myynti-taulukossa Myyntisumma-sarakkeen ja luomme sitten eksplisiittisen Myyntisummien summa -mittayksikön valitsemalla Summa-toiminnon. Muista, että eksplisiittinen mittayksikkö luodaan Power Pivotissa taulukon laskenta-alueelle. Teemme saman Myytyjen tuotteiden kustannukset -sarakkeeseen. Annamme niille tunnistamisen helpottamiseksi nimetYhteenlaskettu myyntisumma ja Yhteenlasketut myytyjen tuotteiden kustannukset.

Sitten luomme toisen mittayksikön tällä kaavalla:

Yhteenlaskettu tuotto : = [ Yhteenlaskettu myyntisumma]–[Yhteenlasketut myytyjen tuotteiden kustannukset]

Kun olemme muuttaneet Yhteenlaskettu tuotto -mittayksikkömme muodon valuutaksi, voimme lisätä sen Pivot-taulukkoomme.

Huomaat, että uuden Yhteenlaskettu tuotto -mittayksikkömme avulla saamme samat tulokset kuin silloin, jos loisimme lasketun Tuotto-sarakkeen ja sijoittaisimme sen ARVOT-alueelle. Erona on, että Yhteenlaskettu tuotto -mittayksikkömme on paljon tehokkaampi ja selkeyttää ja yksinkertaistaa tietomalliamme, koska suoritamme laskutoimituksen vain Pivot-taulukkoa varten valituille kentille. Emme lopulta tarvitse laskettua Tuotto-saraketta lainkaan.

Miksi tämä viimeinen osa on tärkeä? Lasketut sarakkeet lisäävät tietoja tietomalliin, ja tiedot vaativat muistia. Jos päivitämme tietomalliin, Tuotto-sarakkeen kaikkien arvojen uudelleenlaskenta vaatii myös käsittelyresursseja. Meidän ei todellisuudessa tarvitse kuluttaa tällaisia resursseja, koska haluamme vain laskea tuoton valitessamme haluamamme Pivot-taulukon tuottokentät, kuten tuoteluokittain, alueittain tai päivämäärittäin.

Katsotaan toista esimerkkiä. Tässä esimerkissä laskettu sarake luo tuloksia, jotka näyttävät ensi silmäyksellä oikeilta, mutta...

Tässä esimerkissä haluamme laskea myyntisummat kokonaismyynnin prosenttiosuutena. Luomme Myynti-taulukkoomme lasketun sarakkeen nimeltä % myynnistä:

Kaavamme toimii näin: Jaa Myynti-taulukon jokaisen rivin Myyntisumma-sarakkeen summa Myyntisumma-sarakkeen kaikkien summien yhteissummalla.

Jos luomme Pivot-taulukon ja lisäämme Tuoteluokka-kentän SARAKKEET-alueelle sekä valitsemme uuden % myynnistä -sarakkeemme, jolloin se sijoittuu ARVOT-alueelle, saamme % myynnistä -yhteissumman jokaista tuoteluokkaamme varten.

Hyvä. Tämä näyttää toistaiseksi hyvältä. Lisätään kuitenkin vielä osittaja. Lisäämme Kalenterivuosi-osittajan ja valitsemme sitten vuoden. Tässä tapauksessa valitsemme vuoden 2007. Saamme tämän.

Ensi silmäyksellä tämä voi näyttää oikealta. Prosenttiosuuksien summan pitäisi kuitenkin olla 100 %, koska haluamme tietää jokaisen tuoteluokan myynnin prosenttiosuuden kokonaismyynnistä vuonna 2007. Mikä siis meni vikaan?

% myynnistä -sarakkeemme laski prosenttiarvon jokaiselle riville, joka on arvo Myyntisumma-sarakkeessa, ja jakoi sen Myyntisumma-sarakkeen kaikkien arvojen yhteissummalla. Lasketun sarakkeen arvot ovat kiinteitä. Ne ovat taulukon jokaisen rivin muuttumaton tulos. Kun lisäsimme % myynnistä -sarakkeen Pivot-taulukkoomme, se koostettiin Myyntisumma-sarakkeen kaikkien arvojen summana. Kyseinen kaikkien arvojen summa % myynnistä -sarakkeessa on aina 100 %.

Voimme poistaa lasketun % myynnistä -sarakkeemme, koska sitä ei ole meille hyötyä. Sen sijaan luomme mittayksikön, joka laskee oikein prosenttiosuuden kokonaismyynnistä riippumatta käytetyistä suodattimista tai osittajista.

Muistatko aiemmin luomamme Yhteenlaskettu myyntisumma -mittayksikön, joka yksinkertaisesti vain laskee yhteen Myyntisumma-sarakkeen arvot? Käytimme sitä argumenttina Yhteenlaskettu tuotto -mittayksikössämme, ja käytämme sitä nyt uudelleen uuden lasketun kenttämme argumenttina.

Luomme uuden mittayksikön seuraavan kaavan avulla:

% kokonaismyynnistä:=([Yhteenlaskettu myyntisumma]) / CALCULATE([Yhteenlaskettu myyntisumma], ALLSELECTED())

Tämä kaava toimii näin: Jaa Yhteenlaskettu myyntisumma Myyntisumma-sarakkeen kokonaissummalla ilman sarake- tai rivisuodattimia, pois lukien Pivot-taulukossa määritetyt suodattimet.

Jos nyt lisäämme uuden % kokonaismyynnistä -mittayksikkömme Pivot-taulukkoon, saamme seuraavan:

Tämä näyttää paremmalta. Nyt jokaisen luokan % kokonaismyynnistä lasketaan prosenttiosuutena kokonaismyynnistä vuonna 2007. Jos valitsemme Kalenterivuosi-osittajassa toisen vuoden tai yli yhden vuoden pituisen ajan, saamme tuoteluokillemme uudet prosenttiosuudet, mutta kokonaissummamme on edelleen 100 %. Voimme lisätä myös muita osittajia ja suodattimia. % kokonaismyynnistä -mittayksikkömme tuottaa tulokseksi aina prosenttiosuuden mahdollisesti käytetyistä osittajista tai suodattimista riippumatta. Mittayksiköiden ansiosta tulos lasketaan aina SARAKKEET- ja RIVIT-alueella määritetyn kontekstin ja mahdollisesti käytettyjen suodattimien tai osittajien mukaan. Mittayksiköiden tehokkuus on siinä.

Seuraavassa on joitakin ohjeita, joiden avulla voit päättää, vastaako laskettu sarake vai mittayksikkö paremmin tietyn laskutoimituksen tarpeita.

Käytä laskettuja sarakkeita

• Jos haluat uusien tietojen näkyvän Pivot-taulukon RIVEILLÄ, SARAKKEISSA tai SUODATTIMISSA tai Power View-visualisoinnin AKSELI-, SELITE- tai VIEREKKÄIN-alueella, sinun on käytettävä laskettua saraketta. Laskettuja sarakkeita voi käyttää tavallisten sarakkeiden tapaan kenttinä millä tahansa alueella, ja jos ne ovat numeerisia, ne voidaan myös koostaa ARVOT-alueelle.

• Jos haluat uusien tietojen olevan rivin kiinteä arvo. Oletetaan esimerkiksi, että sinulla on päivämääräsarakkeen sisältävä päivämäärätaulukko, ja haluat toisen sarakkeen, joka sisältää vain kuukauden numeron. Voit luoda lasketun sarakkeen, joka laskee vain kuukauden numeron Päivämäärä-sarakkeen päivämääristä. Esimerkki: = KUUKAUSI('Päivämäärä'[Päivämäärä]).

• Jos haluat lisätä taulukon jokaiselle riville tekstiarvon, käytä laskettua saraketta. Kenttiä, joissa on tekstiarvo, ei voida koskaan koostaa ARVOT-alueelle. Esimerkki: =MUOTOILE('Päivämäärä'[Päivämäärä],”kkkk”) antaa kuukauden nimen Päivämäärä-taulukon Päivämäärä-sarakkeen jokaiselle päivämäärälle.

Käytä mittayksiköitä

• Jos laskutoimituksen tulos riippuu aina muista Pivot-taulukosta valitsemistasi kentistä.

• Jos sinun pitää suorittaa monimutkaisia laskutoimituksia, kuten laskea määrä jonkin suodattimen perusteella tai laskea vuosivertailu tai varianssi, käytä laskettua kenttää.

• Jos haluat pitää työkirjan koon mahdollisimman pienenä ja maksimoida sen tehokkuuden, luo mahdollisimman monia laskutoimituksia mittayksiköinä. Usein kaikki laskutoimitukset voivat olla mittayksiköitä, mikä pienentää työkirjan kokoa ja lyhentää päivitysaikaa huomattavasti.

Pidä mielessä, että voit aivan hyvin luoda laskettuja sarakkeita samaan tapaan kuin loimme niitä Tuotto-sarakkeen yhteydessä, ja sitten koostaa ne Pivot-taulukkoon tai raporttiin. Se on itse asiassa hyvä ja helppo tapa tutustua laskutoimituksiin ja luoda niitä. Kun ymmärryksesi näistä erittäin tehokkaista Power Pivotin toiminnoista kasvaa, haluat ehkä luoda mahdollisimman tehokkaan ja täsmällisen ja tietomallin. Toivottavasti tässä oppimastasi on apua. Saatavilla on myös joitakin muita todella hyviä resursseja, joista voi olla apua. Seuraavassa on muutamia niistä: Context in DAX Formulas (englanniksi), Aggregations in Power Pivot (englanniksi) ja DAX Resource Center. Profit and Loss Data Modeling and Analysis with Microsoft Power Pivot in Excel (englanniksi) on hieman edistyneempi, kirjanpidon ja rahoituksen ammattilaisille tarkoitettu malli, mutta siinä on paljon hyviä esimerkkejä tietomallinnuksesta ja kaavoista.