Het gebruik van berekende kolommen en berekende velden

Wanneer gebruikers leren hoe ze Power Pivot moeten gebruiken, ontdekken ze dat de werkelijke kracht ligt in het aggregeren of berekenen van een resultaat. Als uw gegevens een kolom bevatten met numerieke waarden, kunt u deze kolom eenvoudig aggregeren door deze te selecteren in een draaitabel of lijst met Power View-velden. Omdat de kolom numeriek is, wordt deze automatisch opgeteld, het gemiddelde berekend, geteld, of welk type aggregatie u ook selecteert. Dit wordt een impliciete meting genoemd. Impliciete metingen zijn heel handig voor snelle en eenvoudige aggregatie, maar ze hebben beperkingen en die beperkingen kunnen bijna altijd worden omzeild met expliciete metingen en berekende kolommen.

Laten we eerst een voorbeeld bekijken waarin we een berekende kolom gebruiken om een nieuwe tekstwaarde toe te voegen voor elke rij in een tabel met de naam Product. Elke rij in de tabel Product bevat allerlei informatie over elk product dat we verkopen. We hebben kolommen voor Productnaam, Kleur, Grootte, Dealerprijs, enzovoort. We hebben een andere gerelateerde tabel met de naam Productcategorie die de kolom ProductCategoryName bevat. Wat we willen is dat voor elk product in de tabel Product de naam van de productcategorie uit de tabel Productcategorie wordt opgenomen. In de tabel Product kunnen we als volgt een berekende kolom met de naam Productcategorie maken:

De nieuwe formule Productcategorie maakt gebruik van de FUNCTIE RELATED DAX om waarden op te halen uit de kolom ProductCategoryName in de gerelateerde tabel Productcategorie en voert deze waarden vervolgens in voor elk product (elke rij) in de tabel Product.

Dit is een goed voorbeeld van hoe een berekende kolom kan worden gebruikt om een vaste waarde toe te voegen voor elke rij die we later kunnen gebruiken in het gebied RIJEN, KOLOMMEN of FILTERS van een draaitabel of in een Power View-rapport.

Laten we nog een voorbeeld maken, waarin we een winstmarge willen berekenen voor onze productcategorieën. Dit is een veelvoorkomend scenario, zelfs in een groot aantal zelfstudies. Ons gegevensmodel bevat een tabel Verkoop met transactiegegevens en er is een relatie tussen de tabel Verkoop en de tabel Productcategorie. In de tabel Verkoop staat een kolom met verkoopbedragen en een kolom met kosten.

We kunnen een berekende kolom maken waarin een winstbedrag voor elke rij wordt berekend door waarden in de COGS-kolom af te trekken van waarden in de kolom SalesAmount. Dit gaat als volgt:

We kunnen nu een draaitabel maken en het veld Productcategorie naar KOLOMMEN slepen en het nieuwe veld Winst naar het gebied WAARDEN (een kolom in een tabel in Power Pivot is een veld in de lijst met draaitabelvelden). Het resultaat is een impliciete meting genaamd Som van Winst. Het is een geaggregeerd bedrag van waarden uit de winstkolom voor elke verschillende productcategorie. Dit levert het volgende resultaat op:

In dit geval is Winst alleen zinvol als een veld in WAARDEN. Als we Winst in het gebied KOLOMMEN zouden plaatsen, zou de draaitabel er als volgt uitzien:

Het veld Winst geeft geen nuttige informatie als het in het gebied KOLOMMEN, RIJEN of FILTERS is geplaatst. Het veld heeft alleen nut als een geaggregeerde waarde in het gebied WAARDEN.

We hebben een kolom gemaakt met de naam Winst waarin een winstmarge wordt berekend voor elke rij in de tabel Verkoop. We hebben vervolgens Winst toegevoegd aan het gebied WAARDEN van de draaitabel, waarbij automatisch een impliciete meting wordt gemaakt en een resultaat wordt berekend voor elke productcategorie. Als u denkt dat we winst voor onze productcategorieën tweemaal hebben berekend, hebt u gelijk. We hebben eerst winst berekend voor elke rij in de tabel Verkoop en we hebben vervolgens Winst toegevoegd aan het gebied WAARDEN waar het is geaggregeerd voor elke productcategorie. Als u ook denkt dat we de berekende kolom Winst niet hadden hoeven maken, hebt u weer gelijk. Maar hoe kunnen we onze winst berekenen zonder een berekende kolom Winst te maken?

Winst kan eigenlijk beter worden berekend als een expliciete meting.

Voor nu laten we de berekende kolom Winst in de tabel Verkoop en Productcategorie in KOLOMMEN en Winst in WAARDEN van de draaitabel om onze resultaten te vergelijken.

In het berekeningsgebied van de tabel Verkoop maken we een meting met de naam Totale winst (om naamgevingsconflicten te vermijden). Uiteindelijk levert dit dezelfde resultaten op als onze eerdere procedure, maar dan zonder een berekende kolom Winst.

In de tabel Verkoop selecteren we eerst de kolom SalesAmount en klikken we vervolgens op AutoSom om een expliciete meting Som van SalesAmount te maken. Let op: een expliciete meting is een meting die we maken in het berekeningsgebied van een tabel in Power Pivot. We doen hetzelfde voor de COGS-kolom. We wijzigen de naam in Totaal SalesAmount en TotaalCOGS om ze eenvoudiger te kunnen onderscheiden.

Vervolgens maken we nog een meting met deze formule:

Totale winst:=[ Totaal SalesAmount]-[Totaal COGS]

Nadat we de opmaak van onze nieuwe meting Totale winst hebben gewijzigd in valuta, kunnen we de meting toevoegen aan de draaitabel.

De nieuwe meting Totale winst geeft dezelfde resultaten als het maken van een berekende kolom Winst en het plaatsen hiervan in WAARDEN. Het verschil is dat onze meting Totale winst veel efficiënter is en ervoor zorgt dat ons gegevensmodel opgeruimder is, omdat we alleen een berekening maken voor de velden die we selecteren voor de draaitabel. We hebben die berekende kolom Winst dus niet nodig.

Waarom is dit laatste belangrijk? Met berekende kolommen worden gegevens aan het gegevensmodel toegevoegd, en gegevens nemen geheugen in beslag. Als we het gegevensmodel vernieuwen, zijn verwerkingsresources ook nodig om alle waarden in de kolom Winst opnieuw te berekenen. Het is niet nodig om dergelijke resources in beslag te nemen, omdat we onze winst willen berekenen wanneer we de velden selecteren waarvoor we Winst willen in de draaitabel, zoals productcategorieën, regio of op datum.

Laten we naar een ander voorbeeld kijken. Een voorbeeld waarin met een berekende kolom resultaten worden geproduceerd die op het eerste oog correct zijn, maar...

In dit voorbeeld willen we verkoopbedragen berekenen als percentage van de totale verkoop. We maken als volgt een berekende kolom met de naam % van Verkoop in de tabel Verkoop:

Met deze formule wordt het volgende aangegeven: Deel voor elke rij in de tabel Verkoop het bedrag in de kolom SalesAmount door het somtotaal van alle bedragen in de kolom SalesAmount.

Als we een draaitabel maken en Productcategorie toevoegen aan KOLOMMEN en onze nieuwe kolom % van Verkoop selecteren om deze in WAARDEN te plaatsen, krijgen we een somtotaal van % van Verkoop voor elke productcategorie.

OK. Dit ziet er tot nu toe prima uit. Maar laten we een slicer toevoegen. We voegen Kalenderjaar toe en selecteren vervolgens een jaar. In dit geval selecteren we 2007. Dit is het resultaat.

Op het eerste gezicht ziet dit er nog steeds correct uit. Maar onze percentages moeten in totaal 100% zijn, omdat we het percentage van de totale verkoop willen weten voor elke productcategorie voor 2007. Dus wat is er verkeerd gegaan?

Met de kolom % van Verkoop is een percentage berekend voor elke rij die de waarde is in de kolom SalesAmount, gedeeld door het somtotaal van alle waarden in de kolom SalesAmount. Waarden in een berekende kolom zijn vast. Ze zijn een onveranderbaar resultaat voor elke rij in de tabel. Toen we % van Verkoop aan de draaitabel toevoegden, werd deze kolom geaggregeerd als een som van alle waarden in de kolom SalesAmount. Deze som van alle waarden in de kolom % van Verkoop is altijd 100%.

We kunnen onze berekende kolom % van Verkoop verwijderen, omdat we hier niets aan hebben. In plaats daarvan gaan we een meting maken waarmee het percentage van totale verkoop correct wordt berekend, ongeacht eventuele filters of slicers die zijn toegepast.

Eerder hebben we de meting Totaal SalesAmount gemaakt, waarmee de kolom SalesAmount gewoonweg wordt opgeteld. We hebben deze gebruikt als een argument in de meting Totale winst en we gaan deze nogmaals gebruiken als een argument in ons nieuwe berekende veld.

We maken een nieuwe meting met de volgende formule:

% van Totale verkoop:=([Totaal SalesAmount]) / CALCULATE ([Totaal SalesAmount], ALLSELECTED())

Met deze formule wordt het volgende aangegeven: Deel het resultaat van Totaal SalesAmount door het somtotaal van SalesAmount zonder kolom- of rijfilters behalve de filters die zijn gedefinieerd in de draaitabel.

Als we nu de nieuwe meting % van totale verkoop aan de draaitabel toevoegen, krijgen we het volgende:

Dat ziet er beter uit. Nu wordt de meting % van totale verkoop voor elke productcategorie berekend als een percentage van de totale verkoop voor het jaar 2007. Als we in de slicer CalenderYear een ander jaar of meerdere jaren selecteren, krijgen we nieuwe percentages voor onze productcategorieën, maar is ons eindtotaal nog altijd 100%. We kunnen ook andere slicers en filters toevoegen. Onze meting % van totale verkoop levert altijd een percentage van de totale verkoop op, ongeacht eventuele slicers of filters die zijn toegepast. Met metingen wordt het resultaat altijd berekend volgens de context die wordt bepaald door de velden in KOLOMMEN en ROWS en door filters of slicers die zijn toegepast. Dit is het grote voordeel van metingen.

Hier volgen enkele richtlijnen om u te helpen bepalen of een berekende kolom of een meting de juiste keuze is voor een bepaalde berekening:

Berekende kolommen gebruiken

• Als u de nieuwe gegevens wilt weergeven in RIJEN, KOLOMMEN of FILTERS in een draaitabel, of op een AS, LEGENDA of TEGEL DOOR in een Power View-visualisatie, moet u een berekende kolom gebruiken. Net als gewone gegevenskolommen kunnen berekende kolommen worden gebruikt als een veld in elk gebied en als ze numeriek zijn, kunnen ze ook worden samengevoegd in WAARDEN.

• Als u wilt dat uw nieuwe gegevens een vaste waarde voor de rij zijn. U hebt bijvoorbeeld een datumtabel met een kolom met datums en u wilt nog een kolom die alleen het nummer van de maand bevat. U kunt een berekende kolom maken waarmee alleen het maandnummer uit de datums in de kolom Datum wordt berekend. Voorbeeld: =MONTH('Date'[Datum]).

• Als u een tekstwaarde aan een tabel wilt toevoegen voor elke rij, gebruikt u een berekende kolom. Velden met tekstwaarden kunnen nooit in WAARDEN worden geaggregeerd. Voorbeeld: =FORMAT('Date'[Datum],"mmmm") geeft ons de naam van de maand voor elke datum in de kolom Datum in de tabel Datum.

Metingen gebruiken

• Als het resultaat van uw berekening altijd afhankelijk is van de andere velden die u selecteert in een draaitabel.

• Als u complexere berekeningen, zoals een telling berekenen op basis van een filter, of een jaar-op-jaar-berekening of een afwijkingsberekening wilt uitvoeren, gebruikt u een berekend veld.

• Als u de werkmap zo klein mogelijk wilt houden en de prestaties wilt maximaliseren, maakt u van zoveel mogelijk berekeningen metingen. In veel gevallen kunnen al uw berekeningen metingen zijn, waardoor de werkbladgrootte aanzienlijk wordt verkleind en de tijd die nodig is voor vernieuwen korter is.

Er is niets mis met het maken van berekende kolommen, zoals we hebben gedaan met onze kolom Winst, en om deze vervolgens te aggregeren in een draaitabel of rapport. Het is een erg goede en eenvoudige manier om meer te leren over berekeningen en uw eigen berekeningen te maken. Naarmate u meer inzicht krijgt in deze twee zeer krachtige functies van Power Pivot, wilt u het meest efficiënte en nauwkeurige gegevensmodel maken. Hopelijk helpt dit artikel u hierbij. Er zijn enkele andere zeer goede informatiebronnen die u ook kunnen helpen. Hier volgen er enkele: Context in DAX-formules, Aggregaties in Power Pivoten DAX Resource Center. En hoewel het een beetje geavanceerder is en is gericht op boekhoud- en financiële professionals, is het voorbeeld Winst- en verliesgegevensmodellering en -analyse met Microsoft Power Pivot in Excel geladen met geweldige voorbeelden van gegevensmodellering en formules.