In Excel 2016 liefern berechnete Felder endlich richtige Ergebnisse

Wer zusätz­li­che Infor­ma­tio­nen aus einer Pivot-Tabel­le gewin­nen möch­te, kann die erfor­der­li­chen Berech­nun­gen über soge­nann­te berech­ne­te Fel­der rea­li­sie­ren. Die­se sind aller­dings nur bei bestimm­ten Berech­nun­gen ein­setz­bar. Eine Mul­ti­pli­ka­ti­on bei­spiels­wei­se kann unsin­ni­ge Ergeb­nis­se lie­fern.

 

Hier schaf­fen die leis­tungs­fä­hi­gen DAX-Mea­su­res Abhil­fe. Mit ihnen las­sen sich auch kom­ple­xe Berech­nun­gen kor­rekt durch­füh­ren. Bis Excel 2013 war zur For­meler­stel­lung noch das Add-In Power Pivot not­wen­dig. In Excel 2016 ist das Hin­zu­fü­gen von DAX-Mea­su­res auch ohne Power Pivot mög­lich.

 

Wo die Pro­ble­me berech­ne­ter Fel­dern lie­gen und wie ein­fach die Lösung mit DAX-Mea­su­res ist, zei­ge ich im fol­gen­den Bei­spiel.

 

Der Pivot-Tabelle ein berechnetes Element hinzufügen

 

Auf Basis der fol­gen­den Daten möch­te ich mit Hil­fe einer Pivot-Tabel­le die Umsät­ze der bei­den Pro­duk­te ermit­teln.

 

01_Tabelle_Bestellungen

Die Tabel­le ent­hält die Bestell­men­gen und Stück­prei­se, aber kei­ne (red­un­dan­te) Spal­te »Umsatz«

 

Den Umsatz ermit­te­le ich mit Hil­fe eines berech­ne­ten Fel­des, das ich über die Regis­ter­kar­te Pivot­T­a­ble-Tool­s/Ana­ly­sie­ren anle­ge.

 

02_03_berechnetes_Feld_anlegen

Mit einem berech­ne­ten Feld lässt sich die Daten­ba­sis schnell ergän­zen

 

Warum das berechnete Feld falsche Ergebnisse liefert

 

Was auf den ers­ten Blick wie eine ein­fa­che Lösung aus­sieht, ent­puppt sich als falsch: Das berech­ne­te Feld mul­ti­pli­ziert die Sum­men von Anzahl und Ein­zel­preis, anstatt die ein­zel­nen Umsät­ze je Pro­dukt auf­zu­ad­die­ren.

 

04_fehlerhafte_Pivot

Anstatt jeweils Anzahl und Ein­zel­preis zu mul­ti­pli­zie­ren und am Ende auf­zu­ad­die­ren, mul­ti­pli­ziert das berech­ne­te Feld die jewei­li­gen Sum­men

 

Um zum rich­ti­gen Ergeb­nis zu kom­men, muss ich in der Daten­ba­sis eine neue Spal­te hin­zu­fü­gen und dort zei­len­wei­se den Umsatz berech­nen. Die­ses Feld ver­wen­de ich anschlie­ßend in der Pivot-Tabel­le:

 

05_Hilfsspalte

Nur mit Hil­fe einer zusätz­li­chen Spal­te in der Daten­ba­sis lässt sich der Umsatz kor­rekt ermit­teln

 

So geht’s ohne Hilfsspalte: Mit einem DAX-Measure den Umsatz korrekt berechnen

 

Bei der oben gezeig­ten Metho­de füge ich der Bestell­ta­bel­le über­flüs­si­ge Daten hin­zu, weil mit der Anzahl und dem Ein­zel­preis bereits alle not­wen­di­gen Infor­ma­tio­nen vor­lie­gen.

 

Dar­über hin­aus stellt sich die Fra­ge, ob über­haupt der Umsatz für jede Zei­le der Tabel­le gebraucht wird? Schließ­lich inter­es­siert doch nur der aggre­gier­te Umsatz, z. B. nach Monat, Kalen­der­jahr oder Waren­grup­pe.

 

Mit einem DAX-Mea­su­re lässt sich der Umsatz auch ohne Hilfs­spal­te berech­nen.

 

Das entscheidende kleine Häkchen: So klappt der Zugriff auf DAX-Formeln

 

Um DAX-Mea­su­res ver­wen­den zu kön­nen, füge ich beim Erstel­len der Pivot-Tabel­le die Daten dem Daten­mo­dell hin­zu.

 

06_Dem_Datenmodell_hinzufuegen

Um die leis­tungs­fä­hi­ge For­mel­spra­che DAX nut­zen zu kön­nen, müs­sen sich die Daten im Daten­mo­dell befin­den

 

In 2 Schritten das Measure erstellen

 

In der Pivot­T­a­ble-Feld­lis­te wäh­le ich per Rechts­klick auf den Tabel­len­na­men im Kon­text­me­nü Mea­su­re hin­zu­fü­gen.

 

07_Measure_hinzufuegen_Feldliste

Schnell und ein­fach lässt sich der Pivot-Tabel­le per Rechts­klick ein Mea­su­re hin­zu­fü­gen

 

Im Dia­log­feld Mea­su­re gebe ich Spei­cher­ort (1) und Name (2) des Mea­su­res, die DAX-For­mel (3) zur Umsatz­be­rech­nung sowie das Zah­len­for­mat (4) ein.

 

08_Measure_Dialog

Mit Hil­fe der SUMX-For­mel wird der Umsatz kor­rekt berech­net

 

Sicher ist sicher: Die Formel auf Fehler prüfen lassen

 

Um sicher­zu­ge­hen, dass mir bei der For­mel­ein­ga­be kein Feh­ler unter­lau­fen ist, kli­cke ich auf DAX-For­mel über­prü­fen (5).

 

09_Syntaxfehler

Wäh­rend Intel­li­Sen­se das Semi­ko­lon als Lis­ten­trenn­zei­chen akzep­tiert, ver­langt die Syn­tax­prü­fung das in den USA gän­gi­ge Kom­ma

 

Darum lehnt Excel die korrekte Formel ab

 

Bei der For­mel­ein­ga­be habe ich natür­lich das in Win­dows fest­ge­leg­te Semi­ko­lon als Trenn­zei­chen für die Para­me­ter ver­wen­det. Genau­so wie es Intel­li­Sen­se von mir ver­langt hat. Die Syn­tax­prü­fung erwar­tet jedoch das in den USA übli­che Kom­ma.

 

Erset­ze ich das Semi­ko­lon durch ein Kom­ma, akzep­tiert Excel mei­ne DAX-For­mel. Hof­fent­lich behebt Micro­soft die­sen Bug schnellst­mög­lich im Rah­men eines der nächs­ten Updates.

 

10_Formel_fehlerfrei

Mit dem Kom­ma als Lis­ten­trenn­zei­chen wird die For­mel als kor­rekt akzep­tiert

 

Die Werkzeuge für Power BI auch live kennenlernen

 

Hier noch ein Tipp für alle, die ihr Know-how zum Umgang mit Power Pivot und Power Que­ry struk­tu­riert und anhand pra­xis­na­her Bei­spie­le auf- und aus­bau­en wol­len: Besu­chen Sie mei­nen Work­shop Zeit­spa­ren­de Pro­fi-Lösun­gen mit Power Que­ry & Power Pivot oder buchen Sie ein Inhou­se-Trai­ning.

 

Die nächs­ten Ter­mi­ne mei­ner offe­nen Trai­nings fin­den Sie hier.

 

Bald als Video: Pivot-Berichte revolutionieren mit dem Excel Datenmodell

Wer Daten fle­xi­bel, kom­pakt und mit hoher Aus­sa­ge­kraft aus­wer­ten möch­te, fin­det in Pivot-Tabel­len und Pivot-Dia­gram­men die geeig­ne­ten Hilfs­mit­tel.

 

Pivot-Aus­wer­tun­gen konn­ten bis­her nur aus den Daten einer ein­zi­gen Tabel­le erstellt wer­den. Sind dann wei­te­re Tabel­len hin­zu­ge­kom­men, wur­den die­se per SVERWEIS in die Daten­ba­sis geholt. Das war umständ­lich und feh­ler­an­fäl­lig. Zudem wur­de Excel bei gro­ßen Daten­men­gen spür­bar lang­sa­mer.

 

Mit den neu­en Mög­lich­kei­ten der Daten­ana­ly­se in Excel beherr­sche ich die Daten­flut noch bes­ser und ent­lo­cke den Daten mei­ner Kun­den die wirk­lich wich­ti­gen und ent­schei­dungs­re­le­van­ten Infor­ma­tio­nen.

 

Die revolutionäre Neuerung seit Excel 2013: Das Datenmodell

 

Im Daten­mo­dell füh­re ich Daten aus meh­re­ren Quel­len zusam­men und ver­knüp­fe die Tabel­len mit­ein­an­der wie in einer rela­tio­na­len Daten­bank. Das Daten­mo­dell ist eine Samm­lung von Tabel­len, die mit­ein­an­der in Bezie­hung ste­hen. Es stellt die Basis mei­ner Pivot-Tabel­len und Pivot-Dia­gram­me dar.

 

In mei­nen Excel-Semi­na­ren stel­le ich aber immer wie­der fest, dass vie­len das Daten­mo­dell gänz­lich unbe­kannt ist. Obwohl es seit Excel 2013 jedem zur Ver­fü­gung steht.

 

Grund genug für mich, ab heu­te bei Video2Brain in Graz ein ent­spre­chen­des Video-Trai­ning auf­zu­zeich­nen!

 

Darum geht es in meinem neuen Video

 

Zuerst demons­trie­re ich, wie mir das Daten­mo­dell den umständ­li­chen SVERWEIS erspart und ich statt­des­sen mit nur einem Maus­klick belie­big vie­le Tabel­len als Daten­ba­sis für mei­ne Pivot-Berich­te her­an­zie­he.

 

Auf­bau­end dar­auf zei­ge ich, wel­che neu­en Ana­ly­se­mög­lich­kei­ten sich mit dem Daten­mo­dell eröff­nen: Von der neu­en Aggre­ga­ti­ons­funk­ti­on Dis­kre­te Anzahl über die ein­fa­che Navi­ga­ti­on mit­tels Schnell­ein­blick bis hin zum Erstel­len leis­tungs­fä­hi­ger berech­ne­ter Fel­der, soge­nann­ter Mea­su­res, mit der For­mel­spra­che DAX.

 

Schließ­lich wer­de ich im Video vor­ma­chen, wie sich das Daten­mo­dell mit Hil­fe des Spe­zi­al­werk­zeugs Power Pivot noch ver­fei­nern, die Benut­zer­freund­lich­keit von Ana­ly­sen erhö­hen, die Repor­ter­stel­lung dank Hier­ar­chi­en schnel­ler erle­di­gen und Pivot-Dia­gram­me noch inter­ak­ti­ver machen las­sen.

 

Und natür­lich gibt es auch noch jede Men­ge Tipps & Tricks, die ich in mei­ner täg­li­chen Arbeit mit Excel, dem Daten­mo­dell und Power Pivot gesam­melt habe.

Vereinfachte Verwaltung von Beziehungen im Datenmodell von Excel 2016

In der Office-Insi­der Ver­si­on gibt’s seit ges­tern eini­ge Neue­run­gen. Zwei gefal­len mir dabei beson­ders gut:

 

1. Die Dia­gramm­sicht des Daten­mo­dells lässt sich nun bequem als Bild spei­chern.
2. Das Dia­log­feld zum Bear­bei­ten von Bezie­hun­gen wur­de deut­lich ver­bes­sert.

 

Das sind die Vorteile des neuen Dialogfelds

Bis­her muss­te ich im Dia­log­feld die Spal­ten aus einer Drop­down­lis­te aus­wäh­len. Ohne Kennt­nis der jewei­li­gen Spal­ten­in­hal­te war das oft schwie­rig und führ­te zu fal­schen Bezie­hun­gen.

 

Jetzt wer­den die Spal­ten mit Daten ange­zeigt. Das macht das Fin­den pas­sen­der Spal­ten zum Kin­der­spiel. Ein­fach die jewei­li­gen Spal­ten ankli­cken. Fer­tig.

 

Beziehungen_verwalten_Excel_2016

Das Fin­den pas­sen­der Spal­ten wird dank des neu­en Dia­log­felds deut­lich ein­fa­cher, weil auch die Spal­ten­in­hal­te ange­zeigt wer­den

 

Alle Neue­run­gen zu Power Pivot in Excel 2016 kön­nen Sie hier nach­le­sen.