Aggregaties in Power Pivot

Aggregaties zijn een manier om gegevens samen te vouwen, samen te vatten of te groeperen. Wanneer u begint met onbewerkte gegevens uit tabellen of andere gegevensbronnen, zijn de gegevens vaak plat, wat betekent dat er veel details zijn, maar deze op geen enkele manier zijn geordend of gegroepeerd. Dit gebrek aan samenvattingen of structuur kan het moeilijk maken om patronen in de gegevens te ontdekken. Een belangrijk onderdeel van gegevensmodellering is het definiëren van aggregaties die patronen vereenvoudigen, abstraheren of samenvatten als antwoord op een specifieke zakelijke vraag.

De meest voorkomende aggregaties, zoals die waarin GEMIDDELDE, AANTAL,DISTINCTCOUNT,MAX,MIN of SOM worden gebruikt, kunnen automatisch in een meting worden gemaakt met behulp van AutoSom. Andere typen aggregaties, zoals AVERAGEX, COUNTX, COUNTROWS of SUMX, retourneren een tabel en hiervoor is een formule vereist die is gemaakt met DAX (Data Analysis Expressions).

Informatie over aggregaties in Power Pivot

Groepen voor aggregatie kiezen

Wanneer u gegevens aggregeert, groepeert u gegevens op kenmerken zoals product, prijs, regio of datum en definieert u vervolgens een formule die werkt op alle gegevens in de groep. Als u bijvoorbeeld een totaal voor een jaar maakt, maakt u een aggregatie. Als u vervolgens een verhouding van dit jaar maakt ten opzichte van het voorgaande jaar en deze weergeeft als percentages, is er sprake van een ander type aggregatie.

De beslissing over de manier waarop de gegevens worden gegroepeerd, wordt bepaald door de bedrijfsvraag. Aggregaties kunnen bijvoorbeeld de volgende vragen beantwoorden:

Aantal Hoeveel transacties waren er in een maand?

Gemiddelden Wat waren de gemiddelde verkopen in deze maand, per verkoper?

Minimum- en maximumwaarden Welke verkooprayons waren de top vijf in termen van verkochte eenheden?

Als u een berekening wilt maken die antwoord geeft op deze vragen, moet u beschikken over gedetailleerde gegevens met de getallen die u wilt tellen of optellen, en moeten numerieke gegevens op een bepaalde manier gerelateerd zijn aan de groepen die u gaat gebruiken om de resultaten te ordenen.

Als de gegevens nog geen waarden bevatten die u kunt gebruiken om ze te groeperen, zoals een productcategorie of de naam van het geografische gebied waarin de winkel zich bevindt, kunt u groepen introduceren in uw gegevens door categorieën toe te voegen. Wanneer u groepen maakt in Excel, moet u handmatig de gewenste groepen typen of selecteren uit de kolommen in het werkblad. In een relationeel systeem worden hiërarchieën zoals productcategorieën echter vaak opgeslagen in een andere tabel dan de feiten- of waardetabel. Meestal is de categorietabel via een of andere sleutel gekoppeld aan de feitengegevens. Stel dat uw gegevens wel product-id's bevatten, maar geen namen van producten of de bijbehorende categorieën. Als u de categorie wilt toevoegen aan een plat Excel-werkblad, moet u de kolom met de categorienamen kopiëren. Met Power Pivot kunt u de tabel met productcategorieën importeren in uw gegevensmodel, een relatie maken tussen de tabel met de numerieke gegevens en de lijst met productcategorieën en vervolgens de categorieën gebruiken om gegevens te groeperen. Zie Een relatie tussen tabellen maken voor meer informatie.

Een functie voor aggregatie kiezen

Nadat u de te gebruiken groeperingen hebt geïdentificeerd en toegevoegd, moet u beslissen welke wiskundige functies u wilt gebruiken voor de aggregatie. Het woord aggregatie wordt vaak gebruikt als synoniem voor de wiskundige of statistische bewerkingen die worden gebruikt bij aggregaties, zoals sommen, gemiddelden, minimum of aantallen. Met Power Pivot kunt u echter aangepaste formules voor aggregatie maken, naast de standaardaggregaties in zowel Power Pivot als Excel.

Zo kunt u bijvoorbeeld met dezelfde set waarden en groeperingen als in de voorgaande voorbeelden aangepaste aggregaties maken die antwoord bieden op de volgende vragen:

Gefilterde tellingen Hoeveel transacties waren er in een maand, exclusief het onderhoudsvenster aan het einde van de maand?

Verhoudingen met gemiddelden in de loop van de tijd Wat was de procentuele groei of daling van de omzet ten opzichte van dezelfde periode vorig jaar?

Gegroepeerde minimum- en maximumwaarden Welke verkooprayons hadden de hoogste positie voor elke productcategorie of voor elke verkooppromotie?

Aggregaties toevoegen aan formules en draaitabellen

Wanneer u een algemeen idee hebt van hoe uw gegevens moeten worden gegroepeerd om zinvol te zijn en met welke waarden u wilt werken, kunt u beslissen of u een draaitabel wilt maken of berekeningen in een tabel wilt maken. Power Pivot breidt de mogelijkheid van Excel uit en verbetert deze om aggregaties zoals sommen, tellingen of gemiddelden te maken. U kunt aangepaste aggregaties maken in Power Pivot binnen het Power Pivot-venster of binnen het Excel-draaitabelgebied.

• In een berekende kolom kunt u aggregaties maken die rekening houden met de context van de huidige rij om gerelateerde rijen op te halen uit een andere tabel, en vervolgens die waarden in de gerelateerde rijen optellen, tellen of het gemiddelde ervan berekenen.
• In een meting kunt u dynamische aggregaties maken waarin zowel filters worden gebruikt die in de formule zijn gedefinieerd, als filters die worden opgelegd door het ontwerp van de draaitabel en de selectie van slicers, kolomkoppen en rijkoppen. Metingen die standaardaggregaties gebruiken, kunnen in Power Pivot worden gemaakt met behulp van AutoSom of door een formule te maken. U kunt ook impliciete metingen maken met behulp van standaardaggregaties in een draaitabel in Excel.

Groepen toevoegen aan een draaitabel

Wanneer u een draaitabel ontwerpt, sleept u velden die groepen, categorieën of hiërarchieën vertegenwoordigen, naar het gedeelte met kolommen en rijen van de draaitabel om de gegevens te groeperen. Vervolgens sleept u velden met numerieke waarden naar het waardengebied, zodat u deze kunt tellen, het gemiddelde ervan kunt berekenen of kunt optellen.

Als u categorieën toevoegt aan een draaitabel, maar de categoriegegevens zijn niet gerelateerd aan de feitengegevens, krijgt u mogelijk een fout of vreemde resultaten. Meestal probeert Power Pivot het probleem op te lossen door automatisch relaties te detecteren en voor te stellen. Zie Werken met relaties in draaitabellen voor meer informatie.

U kunt ook velden slepen naar slicers om bepaalde groepen gegevens te selecteren voor de weergave. Met slicers kunt u de resultaten interactief groeperen, sorteren en filteren in een draaitabel.

Werken met groeperingen in een formule

U kunt ook groeperingen en categorieën gebruiken om gegevens in tabellen samen te voegen door relaties tussen tabellen te maken en vervolgens formules te maken die gebruikmaken van deze relaties om gerelateerde waarden op te zoeken.

Met andere woorden: als u een formule wilt maken waarmee waarden op een categorie worden gegroepeerd, maakt u eerst gebruik van een relatie om de tabel met de detailgegevens te verbinden met de tabellen met de categorieën en maakt u vervolgens de formule.

Zie Lookups in Power Pivot-formules voor meer informatie over het maken van formules waarin lookups worden gebruikt.

Filters gebruiken in aggregaties

Een nieuwe functie in Power Pivot is de mogelijkheid filters toe te passen op kolommen en tabellen met gegevens, niet alleen in de gebruikersinterface en in een draaitabel of grafiek, maar ook in de formules die u gebruikt om aggregaties te berekenen. Filters kunnen worden gebruikt in formules in zowel berekende kolommen als in s.

In de nieuwe aggregatiefuncties van DAX kunt u bijvoorbeeld in plaats van waarden op te geven waarover moet worden opgeteld of geteld, een hele tabel opgeven als het argument. Als u geen filters op deze tabel toepast, zou de aggregatiefunctie alle waarden in de opgegeven kolom van de tabel tegenwerken. In DAX kunt u echter een dynamisch of statisch filter op de tabel maken, zodat de aggregatie afhankelijk van de filtervoorwaarde en de huidige context op een andere subset van gegevens werkt.

Door voorwaarden en filters in formules te combineren, kunt u aggregaties maken die veranderen afhankelijk van de waarden in formules of die veranderen afhankelijk van de selectie van rijen, koppen en kolomkoppen in een draaitabel.

Zie Gegevens in formules filteren voor meer informatie.

Vergelijking van statistische Excel-functies en statistische DAX-functies

De volgende tabel bevat enkele standaardaggregatiefuncties van Excel en koppelingen naar de implementatie van deze functies in Power Pivot. De DAX-versie van deze functies gedraagt zich vrijwel hetzelfde als de Excel-versie, met enkele kleine verschillen in syntaxis en verwerking van bepaalde gegevenstypen.

Standaard aggregatiefuncties

Functie	Gebruik
GEMIDDELDE	Geeft als resultaat het gemiddelde (rekenkundig gemiddelde) van alle getallen in een kolom.
AVERAGEA	Geeft als resultaat het gemiddelde (rekenkundig gemiddelde) van alle waarden in een kolom. Verwerkt tekst en niet-numerieke waarden.
AANTAL	Telt het aantal numerieke waarden in een kolom.
AANTALARG	Telt het aantal waarden in een kolom die niet leeg zijn.
MAX	Geeft als resultaat de grootste numerieke waarde in een kolom.
MAXX	Retourneert de grootste waarde uit een set expressies die voor een tabel wordt geëvalueerd.
MIN	Geeft als resultaat de kleinste numerieke waarde in een kolom.
MINX	Retourneert de kleinste waarde uit een set expressies die voor een tabel wordt geëvalueerd.
SOM	Telt alle getallen in een kolom bij elkaar op.

Functies voor DAX-aggregatie

DAX bevat aggregatiefuncties waarmee u de tabel kunt opgeven waarop de aggregatie moet worden uitgevoerd. Dus in plaats van alleen de waarden in een kolom op te tellen of het gemiddelde ervan te berekenen, kunt u met deze functies een expressie maken die de gegevens die moeten worden samengevoegd dynamisch definieert.

De volgende tabel bevat de aggregatiefuncties die in DAX beschikbaar zijn.

Functie	Gebruik
GEMIDDELDEX	Berekent het gemiddelde van een set expressies die voor een tabel wordt geëvalueerd.
COUNTAX	Telt een reeks expressies die wordt geëvalueerd voor een tabel.
AANTAL.LEGE.CELLEN	Telt het aantal lege waarden in een kolom.
COUNTX	Telt het totale aantal rijen in een tabel.
COUNTROWS	Hiermee wordt het aantal rijen geteld dat wordt geretourneerd door een geneste tabelfunctie, zoals de filterfunctie.
SUMX	Retourneert de som van een set expressies die wordt geëvalueerd voor een tabel.

Verschillen tussen de aggregatiefuncties van DAX en Excel

Hoewel deze functies dezelfde namen hebben als hun Excel-tegenhangers, maken ze gebruik van de analyse-engine in het geheugen van Power Pivot en zijn ze herschreven om te werken met tabellen en kolommen. U kunt geen DAX-formule gebruiken in een Excel-werkmap en omgekeerd. Ze kunnen alleen worden gebruikt in het Power Pivot-venster en in draaitabellen die zijn gebaseerd op Power Pivot-gegevens. Hoewel de functies identieke namen hebben, kan het gedrag ook iets afwijken. Zie voor meer informatie de onderwerpen over de afzonderlijke functieverwijzingen.

De manier waarop kolommen in een aggregatie worden geëvalueerd, verschilt ook van de manier waarop aggregaties in Excel worden verwerkt. Een voorbeeld kan dit illustreren.

Stel dat u een som wilt weten van de waarden in de kolom Amount in de tabel Sales, en maak de volgende formule:

=SUM('Sales'[Amount])

In het eenvoudigste geval haalt de functie de waarden op uit één niet-gefilterde kolom en is het resultaat hetzelfde als in Excel, waarin altijd alleen de waarden in de kolom Bedrag worden opgeteld. In Power Pivot wordt de formule echter geïnterpreteerd als "Haal de waarde in Bedrag op voor elke rij van de tabel Verkoop en tel vervolgens deze afzonderlijke waarden bij elkaar op. Power Pivot evalueert elke rij waarop de aggregatie wordt uitgevoerd en berekent één scalaire waarde voor elke rij. Vervolgens wordt een aggregatie op deze waarden uitgevoerd. Het resultaat van een formule kan dus anders zijn als filters zijn toegepast op een tabel of als de waarden worden berekend op basis van andere aggregaties die mogelijk worden gefilterd. Zie Context in DAX-formules voor meer informatie.

DAX Time Intelligence-functies

Naast de tabelaggregatiefuncties die in de vorige sectie zijn beschreven, beschikt DAX over aggregatiefuncties die werken met datums en tijden die u opgeeft om ingebouwde time intelligence te verschaffen. Deze functies gebruiken bereiken van datums om gerelateerde waarden op te halen en de waarden te aggregeren. U kunt ook waarden in verschillende datumbereiken vergelijken.

De volgende tabel bevat de tijdintelligentiefuncties die kunnen worden gebruikt voor aggregatie.

Functie	Gebruik
CLOSINGBALANCEMONTH SLUITENBALANSKWARTAAL CLOSINGBALANCEYEAR	Berekent een waarde aan het kalendereinde van de opgegeven periode.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Berekent een waarde aan het kalendereinde van de periode voorafgaand aan de opgegeven periode.
TOTALMTD TOTALYTD TOTAALQTD	Berekent een waarde over het interval dat begint op de eerste dag van de periode en eindigt op de laatste datum in de opgegeven datumkolom.

De andere functies in de sectie Tijdintelligentiefunctie (Tijdintelligentiefuncties) zijn functies die kunnen worden gebruikt om datums of aangepaste datumbereiken op te halen voor aggregatie. U kunt bijvoorbeeld de functie DATESINPERIOD gebruiken om een datumbereik te retourneren en die set datums gebruiken als argument voor een andere functie om een aangepaste aggregatie voor alleen die datums te berekenen.