Koosteet PowerPivotissa

Koostaminen on tapa tiivistää, esittää yhteenvetoja tai ryhmitellä tietoja. Kun aloitat taulukoiden tai muiden tietolähteiden raakatiedoista, tiedot ovat usein tasaisia eli niissä on paljon yksityiskohtia, mutta niitä ei ole järjestetty tai ryhmitelty millään tavalla. Yhteenvetojen tai rakenteen puute voi vaikeuttaa tietojen kaavojen löytämistä. Tärkeä osa tietojen mallintamista on määrittää koosteita, joilla yksinkertaistetaan, abstraktoidaan tai tiivistetään malleja vastauksena tiettyyn liiketoimintaan liittyvään kysymykseen.

Yleisimmät koosteet, kuten KESKIARVO-, LASKE-, DISTINCTCOUNT-, MAX-, MIN- tai SUMMA-funktiot, voidaan luoda mittayksikössä automaattisesti Automaattinen summa -toiminnon avulla. Muut koostetyypit, kuten AVERAGEX, COUNTX, COUNTROWS tai SUMX , palauttavat taulukon ja edellyttävät DAX (Data Analysis Expressions) -kieltä käyttämällä luotua kaavaa.

Tietoja koosteista PowerPivotissa

Koosteryhmien valitseminen

Kun koostat tietoja, ryhmittelet tiedot määritteen, kuten tuotteen, hinnan, alueen tai päivämäärän, mukaan ja määrität sitten kaavan, jota käytetään ryhmän kaikkien tietojen kohdalla. Kun esimerkiksi luot vuoden kokonaissumman, luot koosteen. Jos tämän jälkeen luodaan tämän vuoden suhdeluku edelliseen vuoteen ja esitetään prosentteina, kyseessä on toisenlainen kooste.

Päätös tietojen ryhmittelystä perustuu liiketoimintaan liittyvään kysymykseen. Koosteet voivat vastata esimerkiksi seuraaviin kysymyksiin:

Määrät Kuinka monta tapahtumaa oli kuukaudessa?

Keskiarvot Mikä oli keskimääräinen myynti tässä kuussa myyjien mukaan?

Vähimmäis- ja enimmäisarvot Mitkä myyntialueet olivat viiden suurimman joukossa myytyjen yksiköiden määrällä mitattuna?

Jotta voit luoda laskutoimituksen, joka vastaa näihin kysymyksiin, sinulla on oltava yksityiskohtaisia tietoja, jotka sisältävät laskettavat tai yhteenlaskettavat luvut, ja näiden numeeristen tietojen on liityttävä jollakin tavalla ryhmiin, joita käytät tulosten järjestämisessä.

Jos tiedoissa ei vielä ole arvoja, joita voit käyttää ryhmittelyyn, kuten tuoteluokkaa tai sen maantieteellisen alueen nimeä, jossa myymälä sijaitsee, voit lisätä tietoihin ryhmiä lisäämällä luokkia. Kun luot ryhmiä Excelissä, sinun on kirjoitettava tai valittava käytettävät ryhmät manuaalisesti laskentataulukon sarakkeista. Relaatiojärjestelmässä hierarkiat, kuten tuoteluokat, tallennetaan kuitenkin usein eri taulukkoon kuin fakta- tai arvotaulukko. Yleensä luokkataulukko on linkitetty faktatietoihin jonkinlaisen avaimen avulla. Oletetaan esimerkiksi, että tietosi sisältävät tuotetunnuksia, mutta et tuotteiden nimiä tai niiden luokkia. Jos haluat lisätä luokan tavalliseen Excel-laskentataulukkoon, sinun on kopioitava se sarakkeeseen, joka sisältää luokkien nimet. Power Pivotilla voit tuoda tuoteluokkataulukon tietomalliin, luoda suhteen numerotiedot sisältävän taulukon ja tuoteluokkaluettelon välille ja ryhmitellä sitten tiedot luokkien avulla. Lisätietoja on artikkelissa Yhteyden luominen taulukoiden välille.

Koostefunktion valitseminen

Kun olet määrittänyt ja lisännyt käytettävät ryhmittelyt, sinun on päätettävä, mitä matemaattisia funktioita käytät koosteeseen. Usein sanaa kooste käytetään synonyyminä koosteissa käytettäville matemaattisille tai tilastotoimille, kuten summille, keskiarvoille, minimiarvoille tai määrille. Power Pivotin avulla voit kuitenkin luoda omia koostekavoja sekä Power Pivotin että Excelin vakiokoosteiden lisäksi.

Jos käytät samoja arvoja ja ryhmittelyjä kuin edellisissä esimerkeissä, voit luoda mukautettuja koosteita, jotka vastaavat seuraaviin kysymyksiin:

Suodatetut määrät Kuinka monta tapahtumaa oli kuukaudessa ilman kuukauden lopun ylläpitoikkunaa?

Suhdeluvut, joissa käytetään keskiarvoja eri aikoina Mikä oli myynnin kasvu tai lasku prosentteina verrattuna viime vuoden vastaavaan ajanjaksoon?

Ryhmitellyt vähimmäis- ja enimmäisarvot Mitkä myyntialueet sijoittuivat kärkisijalle kussakin tuoteluokassa tai myynninedistämisessä?

Koosteiden lisääminen kaavoihin ja Pivot-taulukoihin

Kun sinulla on käsitys siitä, miten tiedot tulisi ryhmitellä ja mitä arvoja haluat käsitellä, voit päättää, haluatko luoda Pivot-taulukon vai luoda laskutoimituksia taulukossa. Power Pivot laajentaa ja parantaa Excelin ominaisuutta luoda koosteita, kuten summia, määriä tai keskiarvoja. Voit luoda mukautettuja koosteita PowerPivotissa joko Power Pivot -ikkunassa tai Excelin Pivot-taulukkoalueella.

• Lasketussa sarakkeessa voit luoda koosteita, jotka ottavat huomioon nykyisen rivikontekstin toisiinsa liittyvien rivien noutamiseksi toisesta taulukosta. Tämän jälkeen voit laskea näiden arvojen summan, määrän tai keskiarvon liittyvillä riveillä.
• Voit luoda mittayksikössä dynaamisia koosteita, jotka käyttävät sekä kaavassa määritettyjä suodattimia että Pivot-taulukon rakenteen sekä osittajien, sarake- ja riviotsikoiden valinnan suodattimia. Vakiokoosteita käyttäviä mittayksiköitä voidaan luoda Power Pivotissa AutoSum-toiminnolla tai luomalla kaava. Voit myös luoda implisiittisiä mittayksiköitä käyttämällä vakiokoosteita Excelin Pivot-taulukossa.

Ryhmittelyjen lisääminen Pivot-taulukkoon

Kun suunnittelet Pivot-taulukkoa, vedä ryhmittelyjä, luokkia tai hierarkioita edustavia kenttiä Pivot-taulukon sarakkeiden ja rivien osaan tietojen ryhmittelyä varten. Vedä sitten numeerisia arvoja sisältävät kentät arvoalueelle niin, että niiden laskenta ja keskiarvo voidaan laskea tai laskea yhteen.

Jos lisäät luokkia Pivot-taulukkoon mutta luokan tiedot eivät liity faktatietoihin, saatat saada virheilmoituksen tai erikoisia tuloksia. Yleensä Power Pivot yrittää korjata ongelman tunnistamalla ja ehdottamalla automaattisesti yhteyksiä. Lisätietoja on artikkelissa Pivot-taulukoiden yhteyksien käsitteleminen.

Voit myös vetää kenttiä osittajiin ja valita tietyt tietoryhmät tarkasteltaviksi. Osittajien avulla voit ryhmitellä, lajitella ja suodattaa Pivot-taulukon tuloksia vuorovaikutteisesti.

Ryhmittelyjen käyttäminen kaavassa

Ryhmittelyjä ja luokkia käyttämällä voit myös koostaa taulukoihin tallennettuja tietoja luomalla suhteita taulukoiden välille luomalla sitten kaavoja, jotka hyödyntävät näitä suhteita toisiinsa liittyvien arvojen hakemiseen.

Toisin sanoen, jos haluat luoda kaavan, joka ryhmittelee arvot luokan mukaan, yhdistä ensin yksityiskohtatiedot sisältävä taulukko luokkia sisältäviin taulukoihin yhteyden avulla ja muodosta sitten kaava.

Lisätietoja hakuja käyttävien kaavojen luomisesta on artikkelissa Haut PowerPivot-kaavoissa.

Suodattimien käyttäminen koosteissa

Power Pivotin uusi ominaisuus on mahdollisuus käyttää suodattimia sarakkeissa ja taulukoissa paitsi käyttöliittymässä ja Pivot-taulukossa tai kaaviossa myös kaavoissa, joita käytetään koosteiden laskemiseen. Suodattimia voidaan käyttää kaavoissa sekä lasketuissa sarakkeissa että s-muodossa.

Esimerkiksi uusissa DAX-koostefunktioissa voit summa- tai laskumääritettävien arvojen määrittämisen sijaan määrittää argumentiksi kokonaisen taulukon. Jos et käyttänyt suodattimia kyseisessä taulukossa, koostefunktio käyttäisi kaikkia taulukon määritetyn sarakkeen arvoja vastaan. DAX-kielessä voit kuitenkin luoda taulukkoon joko dynaamisen tai staattisen suodattimen, jolloin kooste toimii eri tietojen alijoukossa suodatinehdon ja nykyisen kontekstin mukaan.

Yhdistämällä ehtoja ja suodattimia kaavoissa voit luoda koosteita, jotka muuttuvat kaavojen tai Pivot-taulukon valittujen rivien, otsikoiden ja sarakeotsikoiden valinnan mukaan.

Lisätietoja on artikkelissa Kaavojen tietojen suodattaminen.

Excel-koostefunktioiden ja DAX-koostefunktioiden vertailu

Seuraavassa taulukossa on lueteltu joitakin Excelin vakiokoostefunktioita ja linkkejä näiden toimintojen toteuttamiseen Power Pivotissa. Näiden funktioiden DAX-versio toimii pitkälti samalla tavalla kuin Excel-versio, mutta tiettyjen tietotyyppien syntaksissa ja käsittelyssä on joitakin pieniä eroja.

Standard Aggregation Functions

Funktio	Yleismerkki
KESKIARVO	Palauttaa sarakkeen kaikkien lukujen aritmeettisen keskiarvon.
KESKIARVOA	Palauttaa sarakkeen kaikkien arvojen aritmeettisen keskiarvon. Käsittelee tekstin ja ei-numeeriset arvot.
LASKE	Laskee sarakkeen numeeristen arvojen määrän.
LASKE.A	Laskee sarakkeen arvojen, jotka eivät ole tyhjiä.
MAKS	Palauttaa sarakkeen suurimman numeerisen arvon.
MAXX	Palauttaa suurimman arvon taulukossa arvioidusta lausekejoukosta.
MIN	Palauttaa sarakkeen pienimmän numeerisen arvon.
MINX	Palauttaa pienimmän arvon taulukon arvioidusta lausekejoukosta.
SUMMA	Laskee yhteen kaikki sarakkeen luvut.

DAX-koostefunktiot

DAX sisältää koostefunktioita, joiden avulla voit määrittää taulukon, johon koostaminen suoritetaan. Sen sijaan, että laskeisit sarakkeen arvojen vain yhteen tai laskeisit keskiarvon, voit näiden funktioiden avulla luoda lausekkeen, joka määrittää dynaamisesti koostettavat tiedot.

Seuraavassa taulukossa on lueteltu DAX-kielessä käytettävissä olevat koostefunktiot.

Funktio	Yleismerkki
KESKIARVOX	Laskee taulukossa arvioidun lausekejoukon keskiarvon.
COUNTAX	Laskee taulukosta arvioidun lausekejoukon.
LASKE.TYHJÄT	Laskee sarakkeen tyhjien arvojen määrän.
COUNTX	Laskee taulukon rivien kokonaismäärän.
COUNTROWS	Laskee sisäkkäisen taulukkofunktion, kuten suodatusfunktion, palauttamien rivien määrän.
SUMX	Palauttaa taulukosta arvioidun lausekejoukon summan.

DAX- ja Excel-koostefunktioiden erot

Vaikka näillä funktioilla on samat nimet kuin Excel-vastineilla, ne hyödyntävät Power Pivotin muistiin varattua analyysimoduulia, ja ne on kirjoitettu uudelleen toimimaan taulukoiden ja sarakkeiden kanssa. DAX-kaavaa ei voi käyttää Excel-työkirjassa ja päinvastoin. Niitä voidaan käyttää vain Power Pivot -ikkunassa ja Power Pivot -tietoihin perustuvissa Pivot-taulukoissa. Vaikka funktioilla on samat nimet, ne saattavat toimia hieman eri tavalla. Lisätietoja on yksittäisten funktioiden ohjeaiheissa.

Myös tapa, jolla sarakkeet arvioidaan koosteessa, eroaa tavasta, jolla Excel käsittelee koosteita. Esimerkki voi auttaa havainnollistamaan.

Jos haluat saada Myynti-taulukon Summa-sarakkeen arvojen summan, luot seuraavan kaavan:

=SUM('Sales'[Amount])

Yksinkertaisimmassa tapauksessa funktio hakee arvot yhdestä suodattamattomasta sarakkeesta, ja tulos on sama kuin Excelissä, joka aina vain laskee yhteen Summa-sarakkeen arvot. Power Pivotissa kaava tulkitaan kuitenkin seuraavasti: "Hae Myynti-taulukon jokaisen rivin arvo summana ja laske sitten kyseiset yksittäiset arvot yhteen. Power Pivot arvioi jokaisen rivin, jolla koostaminen suoritetaan, laskee jokaiselle riville yhden skalaariarvon ja suorittaa sitten arvoille koosteen. Kaavan tulos voi siis olla eri, jos taulukossa on käytetty suodattimia tai jos arvot lasketaan muiden mahdollisesti suodatettujen koosteiden perusteella. Lisätietoja on kohdassa DAX-kaavojen konteksti.

DAX-kielen aikatietofunktiot

Edellisessä osiossa kuvattujen taulukkokoostefunktioiden lisäksi DAX-kielessä on koostefunktioita, jotka toimivat määrittämiesi päivämäärien ja kellonaikojen kanssa ja tarjoavat valmiita aikatietoja. Nämä funktiot hakevat toisiinsa liittyviä arvoja ja koostavat arvot päivämääräalueiden avulla. Voit myös verrata päivämääräalueiden arvoja.

Seuraavassa taulukossa on lueteltu aikatietofunktiot, joita voidaan käyttää koostamiseen.

Funktio	Yleismerkki
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	Laskee arvon annetun ajanjakson kalenterilopussa.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER-VUOSINELJÄNNES OPENINGBALANCEYEAR	Laskee arvon annettua kautta edeltävän kauden kalenterilopussa.
SUMMAMTD TOTALYTD TOTALQTD	Laskee arvon ajanjaksolta, joka alkaa kauden ensimmäisestä päivästä ja päättyy määritetyn päivämääräsarakkeen viimeiseen päivämäärään.

Aikatietofunktio-osan muut funktiot (aikatietofunktiot) ovat funktioita, joilla voidaan noutaa päivämääriä tai mukautettuja päivämääräalueita koostetta varten. Voit esimerkiksi palauttaa päivämääräalueen DATESINPERIOD-funktiolla ja käyttää tätä päivämääräjoukkoa argumenttina toiselle funktiolle, kun haluat laskea mukautetun koosteen vain kyseisille päivämäärille.