{"id":465,"date":"2014-08-26T18:07:18","date_gmt":"2014-08-26T16:07:18","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=465"},"modified":"2014-08-26T18:16:39","modified_gmt":"2014-08-26T16:16:39","slug":"wasserfall-diagramme-mit-powerpivot","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=465","title":{"rendered":"Wasserfall Diagramme mit PowerPivot"},"content":{"rendered":"<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2013\/04\/PowerPivot_Logo.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft  wp-image-270\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2013\/04\/PowerPivot_Logo.png\" alt=\"PowerPivot_Logo\" width=\"72\" height=\"73\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2013\/04\/PowerPivot_Logo.png 252w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2013\/04\/PowerPivot_Logo-150x150.png 150w\" sizes=\"(max-width: 72px) 100vw, 72px\" \/><\/a><a href=\"#en\">(english version below)<\/a><br \/>\nNachdem ich bisher Wasserfall Diagramme manuell in Excel erstellt habe, m\u00f6chte ich hier zeigen, wie das auch in Verbindung mit PowerPivot f\u00fcr Excel funktioniert. Die Datengrundlage ist die gute alte Northwind Datenbank. Um ein Wasserfall Diagramm zu erstellen, ben\u00f6tige ich folgende Informationen:<\/p>\n<ul>\n<li>Aktueller Umsatz<\/li>\n<li>Umsatz des letzten Monats\n<ul>\n<li>Verringerung und<\/li>\n<li>Vergr\u00f6\u00dferung des Umsatz<\/li>\n<\/ul>\n<\/li>\n<li>Zeit Informationen<\/li>\n<\/ul>\n<p>Mit folgendem Query lese ich die Daten und l\u00f6se das Pivot auf, damit ich die Daten besser in PowerPivot verarbeiten kann:<\/p>\n<pre>SELECT\r\n\u00a0YearNumber\r\n\u00a0, MonthNumber \r\n\u00a0, Amount\r\n\u00a0, AmountType\r\n\u00a0, CASE AmountType\r\n\u00a0\u00a0WHEN 'BaseValue' THEN 1\r\n\u00a0\u00a0WHEN 'Increase' THEN 2\r\n\u00a0\u00a0WHEN 'Reduction' THEN 3\r\n\u00a0END As CategorySort\r\nFROM (\u00a0SELECT \r\n\u00a0\u00a0CASE \r\n\u00a0\u00a0\u00a0WHEN Data.SalesAmount - Data.SalesAmountLastMonth\u00a0 &lt; 0 \r\n\u00a0\u00a0\u00a0\u00a0THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 \r\n\u00a0\u00a0\u00a0\u00a0ELSE 0\r\n\u00a0\u00a0\u00a0END As Reduction\r\n\u00a0\u00a0, Data.SalesAmountLastMonth - \r\n\u00a0\u00a0\u00a0CASE \r\n\u00a0\u00a0\u00a0WHEN Data.SalesAmount - Data.SalesAmountLastMonth\u00a0 &lt; 0 \r\n\u00a0\u00a0\u00a0\u00a0THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 \r\n\u00a0\u00a0\u00a0\u00a0ELSE 0\r\n\u00a0\u00a0\u00a0END As BaseValue\r\n\u00a0\u00a0, CASE \r\n\u00a0\u00a0\u00a0WHEN Data.SalesAmount - Data.SalesAmountLastMonth\u00a0 &gt; 0 \r\n\u00a0\u00a0\u00a0\u00a0THEN (Data.SalesAmount - Data.SalesAmountLastMonth)\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0ELSE 0\r\n\u00a0\u00a0\u00a0END As Increase\r\n\u00a0\u00a0, Data.MonthNumber\r\n\u00a0\u00a0, Data.YearNumber\r\n\u00a0FROM (\r\n\u00a0\u00a0SELECT\r\n\u00a0\u00a0\u00a0MONTH(Orders.OrderDate) As MonthNumber\r\n\u00a0\u00a0\u00a0, YEAR(Orders.OrderDate) As YearNumber\r\n\u00a0\u00a0\u00a0, SUM(Quantity*UnitPrice) As SalesAmount\r\n\u00a0\u00a0\u00a0, (\r\n\u00a0\u00a0\u00a0\u00a0SELECT SUM(od1.Quantity*od1.UnitPrice) FROM [dbo].[Orders] o1\r\n\u00a0\u00a0\u00a0\u00a0INNER JOIN [dbo].[Order Details] od1 ON od1.OrderID = o1.OrderID\r\n\u00a0\u00a0\u00a0\u00a0WHERE MONTH(o1.OrderDate) = MONTH(DATEADD(mm, -1, MAX(Orders.OrderDate))) \r\n\u00a0\u00a0\u00a0\u00a0AND YEAR(o1.OrderDate) = YEAR(DATEADD(mm, -1, MAX(Orders.OrderDate)))\r\n\u00a0\u00a0\u00a0) As SalesAmountLastMonth\r\n\u00a0\u00a0\u00a0 FROM [dbo].[Orders]\r\n\u00a0\u00a0\u00a0 INNER JOIN [dbo].[Order Details] ON [Order Details].OrderID = Orders.OrderID\r\n\u00a0\u00a0\u00a0 WHERE Year(Orders.OrderDate) = 1997\r\n\u00a0\u00a0\u00a0 GROUP BY \r\n\u00a0\u00a0\u00a0MONTH(Orders.OrderDate) \r\n\u00a0\u00a0\u00a0, YEAR(Orders.OrderDate)\r\n\u00a0\u00a0) data\r\n\u00a0) t\r\n\u00a0UNPIVOT (Amount for AmountType in (Reduction, BaseValue, Increase)) As Amount\r\nORDER BY YearNumber, MonthNumber<\/pre>\n<p>&nbsp;<\/p>\n<p>Nachdem diese nun eingelesen wurden sind, habe ich zu jedem Monat 3 Zeilen mit den notwendigen Informationen. Nun lasse ich die jeweilige Kategorie noch sortieren, damit der Basis Wert im Diagramm unten liegt. \u00dcbergibt man nun die Daten in ein gestapeltes Diagramm, sieht das folgenderma\u00dfen aus:<\/p>\n<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/1.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-469\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/1.png\" alt=\"1\" width=\"575\" height=\"414\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/1.png 575w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/1-300x216.png 300w\" sizes=\"(max-width: 575px) 100vw, 575px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/2.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-466\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/2.png\" alt=\"2\" width=\"538\" height=\"231\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/2.png 538w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/2-300x128.png 300w\" sizes=\"(max-width: 538px) 100vw, 538px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/3.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-467\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/3.png\" alt=\"3\" width=\"1253\" height=\"417\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/3.png 1253w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/3-300x99.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/3-1024x340.png 1024w\" sizes=\"(max-width: 1253px) 100vw, 1253px\" \/><\/a><\/p>\n<p>Nun m\u00fcssen lediglich folgende Anpassungen gemacht werden:<\/p>\n<ul>\n<li>Die Kategorie mit dem Basis Wert ohne Farbe einstellen<\/li>\n<li>Verlustwerte rot f\u00e4rben<\/li>\n<li>Gewinnwerte gr\u00fcn f\u00e4rben<\/li>\n<li>Die Beschriftungen ausblenden<\/li>\n<\/ul>\n<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/4.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-468\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/4.png\" alt=\"4\" width=\"627\" height=\"290\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/4.png 627w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/4-300x138.png 300w\" sizes=\"(max-width: 627px) 100vw, 627px\" \/><\/a><\/p>\n<p>Voila! Fertig ist das Wasserfall Diagramm mit Hilfe von PowerPivot.<\/p>\n<p>&nbsp;<\/p>\n<h1><a name=\"en\"><\/a>Waterfall Chart with PowerPivot<\/h1>\n<p>&nbsp;<\/p>\n<p>After I&#8217;ve created waterfall charts manually in Excel, I want to show here how this works in conjunction with PowerPivot for Excel. The data\u00a0is from the good old Northwind database.<\/p>\n<p>To create a waterfall chart, I need the following information:<\/p>\n<ul>\n<li>Current sales<\/li>\n<li>Turnover last month<\/li>\n<li>reduction and increase in the sales<\/li>\n<li>Time information<\/li>\n<\/ul>\n<p>With the following query I read the data and make a\u00a0unpivot so I can better handle the data in PowerPivot:<\/p>\n<pre>SELECT\r\n YearNumber\r\n , MonthNumber \r\n , Amount\r\n , AmountType\r\n , CASE AmountType\r\n  WHEN 'BaseValue' THEN 1\r\n  WHEN 'Increase' THEN 2\r\n  WHEN 'Reduction' THEN 3\r\n END As CategorySort\r\nFROM ( SELECT \r\n  CASE \r\n   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  &lt; 0 \r\n    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 \r\n    ELSE 0\r\n   END As Reduction\r\n  , Data.SalesAmountLastMonth - \r\n   CASE \r\n   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  &lt; 0 \r\n    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 \r\n    ELSE 0\r\n   END As BaseValue\r\n  , CASE \r\n   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  &gt; 0 \r\n    THEN (Data.SalesAmount - Data.SalesAmountLastMonth)  \r\n    ELSE 0\r\n   END As Increase\r\n  , Data.MonthNumber\r\n  , Data.YearNumber\r\n FROM (\r\n  SELECT\r\n   MONTH(Orders.OrderDate) As MonthNumber\r\n   , YEAR(Orders.OrderDate) As YearNumber\r\n   , SUM(Quantity*UnitPrice) As SalesAmount\r\n   , (\r\n    SELECT SUM(od1.Quantity*od1.UnitPrice) FROM [dbo].[Orders] o1\r\n    INNER JOIN [dbo].[Order Details] od1 ON od1.OrderID = o1.OrderID\r\n    WHERE MONTH(o1.OrderDate) = MONTH(DATEADD(mm, -1, MAX(Orders.OrderDate))) \r\n    AND YEAR(o1.OrderDate) = YEAR(DATEADD(mm, -1, MAX(Orders.OrderDate)))\r\n   ) As SalesAmountLastMonth\r\n    FROM [dbo].[Orders]\r\n    INNER JOIN [dbo].[Order Details] ON [Order Details].OrderID = Orders.OrderID\r\n    WHERE Year(Orders.OrderDate) = 1997\r\n    GROUP BY \r\n   MONTH(Orders.OrderDate) \r\n   , YEAR(Orders.OrderDate)\r\n  ) data\r\n ) t\r\n UNPIVOT (Amount for AmountType in (Reduction, BaseValue, Increase)) As Amount\r\nORDER BY YearNumber, MonthNumber<\/pre>\n<p>After I imported the data, I have 3 lines for each month with the necessary information. Now I let the respective category yet sort, so that the base value is in the chart below. Then we can create a stacked bar chart:<br \/>\n<a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/1.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-469\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/1.png\" alt=\"1\" width=\"575\" height=\"414\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/1.png 575w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/1-300x216.png 300w\" sizes=\"(max-width: 575px) 100vw, 575px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/2.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-466\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/2.png\" alt=\"2\" width=\"538\" height=\"231\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/2.png 538w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/2-300x128.png 300w\" sizes=\"(max-width: 538px) 100vw, 538px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/3.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-467\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/3.png\" alt=\"3\" width=\"1253\" height=\"417\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/3.png 1253w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/3-300x99.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/3-1024x340.png 1024w\" sizes=\"(max-width: 1253px) 100vw, 1253px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Now only the following adjustments must be made\u200b\u200b:<br \/>\n&#8211; Set the category to the base value without color<br \/>\n&#8211;\u00a0Reduction is set to red<br \/>\n&#8211; Increase is set to \u200b\u200bgreen<br \/>\n&#8211; Hide the Labels<\/p>\n<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/4.png\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-468\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/4.png\" alt=\"4\" width=\"627\" height=\"290\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/4.png 627w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2014\/08\/4-300x138.png 300w\" sizes=\"(max-width: 627px) 100vw, 627px\" \/><\/a><\/p>\n<p>Voila! Finish is the waterfall chart using PowerPivot.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(english version below) Nachdem ich bisher Wasserfall Diagramme manuell in Excel erstellt habe, m\u00f6chte ich hier zeigen, wie das auch in Verbindung mit PowerPivot f\u00fcr Excel funktioniert. Die Datengrundlage ist die gute alte Northwind Datenbank. Um ein Wasserfall Diagramm zu &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=465\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":270,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[30,29,41,26],"tags":[46,44],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/465"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=465"}],"version-history":[{"count":6,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/465\/revisions"}],"predecessor-version":[{"id":475,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/465\/revisions\/475"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/media\/270"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=465"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=465"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=465"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}