en en ms fi da da da da ca da da da ms hbs_lat ms ms ms en da da ms ms da es da de ro en da da da da de da da de en it da da da es da da en da da da en da sv de da en da en en en en en da pt fr fr en de en sv da da da da sv da da en en en nl en nl en en en en en en en en en en en en en en en en tr en en es en en en en en en en en en en en en en en es en en en en en en tr en en en es en en sco en en en en en en en hbs_lat en en nl da da da da da en da da da da da en tr da ca en en id en en en en en en en en ms ms ms en en ml da nl en en nl nl da en da de de en en nl en en nl nl sv en da de de en en nl en en nl nl sv en da de de en en nl en en sv nl da en da de da en en nl en en nl nl sv en da da da en en nl en en nl nl sv en da da da en en nl en en nl nl sv en da de de en en nl en en nl nl da en da de de en en nl en en nl nl da en da de de en en nl en en nl en da ms en en en sv da fr da it de en ms ms en it da da de en en en sv da en da en da da en da da da hbs_lat da de de de el de en en tr en en en en en en en es es fi fr fr fr fr hu it it ja ko da nl nl pl pt pt ru sv th tr zh zh ar he
Getting Started with Power Pivot – Part II | Microsoft Power BI-blog | Microsoft Power BI
Office 365 + Power BI
Welcome back to our Getting Started with Excel and Power BI Series.
In Part I of Getting Started with Power Pivot we created our first Excel Data Model. Today we will create additional relationships and start using DAX to add business logic to our Model.
To follow this tutorial you will need:
After making te data show up using te right relationships in part I, we want improve te Pivot Table as we want to slice my data by year. To do this I’ll add a slicer to my worksheet:
Clicking te slicer doesn’t do anything yet as there again no relationship between te Invoice and DateTable. After inspecting te fields in both tables I find out that there is no field in te Invoice table that I can use to relate with. Luckily we can create a field in te table that allows us to create te relationship.
To work with te Data model In Excel 2013 we use te Power Pivot add-in. This add in is installed by default in many versions of Excel, you just need to enable it.
Now that we have enabled te Power Pivot add in a new ribbon will show up in Excel:
To work with this model I click te “Manage” button, this will open te Power Pivot dialog:
Te initial dialog that opens shows each tables in te Model with all te rows of data, in this case it shows te DateTable. You can switch between tables by clicking te table tabs at te bottom:
I now select te Invoice table where I want to create a column that adds a Date value to for each row of data.  I do right mouse click on te CalendarYearMonth column and select “Insert Column”.
This creates a calculated column in te Invoice table where I use a formula to populate te column with values, I combine values from te CalendarYearMonth column to create a new date value using te following formula:
As you might notice te formula used here is very similar to te formula’s you would write in Excel, this on purpose, te DAX language used in Power Pivot is designed to be familiar to te Excel users. It has some differences that are mostly focused around capabilities that are not supported by Excel like traversing relationships and special built-in functionality for calculations.
Adding te calculation to te data model will give us a new column that we can use in te relationship:
I then rename te column to InvoiceDate. To create te relationship I can also move over to te diagram view in te Power Pivot add-in and drag and drop te fields that I want to create a relationship between. Dragging and dropping results in a relationship being created:
If we now go back to our PivotTable and we now see te results we expect:
Now I want to compare te RevenueAmount for te selected year with te RevenueAmount for te previous year. In order for me to do this I can again use a DAX formula. In this case I want to create a calculated field in Excel to add to my PivotTable.
I go to te Power Pivot ribbon and click Calculated Fields, “New Calculated Field”:
This opens a new dialog that allows me add a new DAX formula that leverages some of te special DAX time intelligence formula’s to calculate te “Sum of RevenueAmount“ for te previous year:
This will add a new field to te PivotTable that shows te RevenueAmount for te previous year compared to te Year selected in te slicer:
Now I can add another calculation that will calculate te Year over Year Growth in percentage:
[Sum of RevenueAmount YoY%] =
In this calculation we divide te Sum of RevenueAmount minus te RevenueAmound previousyear by te RevenueAmount previousyear that results in te percentage growth. Adding this calculated field to te PivotTable gives us te percentage of growth for revenueamount:
I hope these blog post gave you a good overview of te capabilities of Power Pivot. Having said that this blog post will be just te beginning of your journey to getting to know Power Pivot and unlocking all its wonders.
See Power BI in action
Follow @MSPowerBI on Twitter
Tilmeld dig Power BI-nyhedsbrevet
Tilmeld dig nedenfor for at modtage de seneste nyheder fra Power BI direkte i din indbakke.
Land Afghanistan Albanien Algeriet Amerikansk Samoa Andorra Angola Anguilla Antarktis Antigua og Barbuda Argentina Armenien Aruba Aserbajdsjan Australien Bahamas Bahrain Bailiwick of Jersey Bangladesh Barbados Belgien Belize Benin Bermuda Bhutan Bolivia Bonaire Bosnien-Hercegovina Botswana Bouvetøen Brasilien Brunei Darussalam Bulgarien Burkina Faso Burundi Cabo Verde Cambodja Cameroun Canada Caymanøerne Chile Cocosøerne (Keelingøerne) Colombia Comorerne Congo Cookøerne Costa Rica Côte d'Ivoire Curaçao Cypern Danmark De Amerikanske Jomfruøer De Amerikanske oversøiske øer De Britiske Jomfruøer De Forenede Arabiske Emirater Den Centralafrikanske Republik Den Demokratiske Republik Congo Den Dominikanske Republik Det Britiske Territorium i Det Indiske Ocean Det palæstinensiske selvstyre Djibouti Dominica Ecuador Egypten El Salvador Eritrea Estland Etiopien Falklandsøerne Fiji Filippinerne Finland Frankrig Fransk Guyana Fransk Polynesien Franske Oversøiske Områder Færøerne Gabon Gambia Georgien Ghana Gibraltar Grenada Grækenland Grønland Guadeloupe Guam Guatemala Guernsey Guinea Guinea-Bissau Guyana Haiti Heard- og McDonaldøerne Honduras Indien Indonesien Iran Irland Island Isle of Man Israel Italien Jamaica Jan Mayen Japan Jordan Juleøen Kasakhstan Kenya Kina Kirgisistan Kiribati Korea Kosovo Kroatien Kuwait Laos Lesotho Letland Libanon Liberia Liechtenstein Litauen Luxembourg Madagaskar Malawi Malaysia Maldiverne Mali Malta Marokko Marshalløerne Martinique Mauretanien Mauritius Mayotte Mexico Mikronesien Moldova Monaco Mongoliet Montenegro Montserrat Mozambique Namibia Nauru Nederlandene Nepal New Zealand Nicaragua Niger Nigeria Niue Nordmakedonien Nordmarianerne Norfolk Island Norge Ny Kaledonien Oman Pakistan Palau Panama Papua Ny Guinea Paraguay Peru Pitcairn Polen Portugal Puerto Rico Qatar Reunion Rumænien Rwanda Saba Saint Barthélemy Saint Kitts og Nevis Saint Lucia Saint Martin Saint Pierre og Miquelon Saint Vincent og Grenadinerne Salomonøerne Samoa San Marino São Tomé og Príncipe SAR Hongkong SAR Macao Saudi-Arabien Schweiz Senegal Serbien Seychellerne Sierra Leone Singapore Sint Eustatius Sint Maarten Slovakiet Slovenien Somalia South Georgia og De Sydlige Sandwichøer Spanien Sri Lanka St. Helena, Ascension, Tristan da Cunha Storbritannien Sudan Surinam Svalbard Sverige Swaziland Sydafrika Sydsudan Syrien Tadsjikistan Taiwan Tanzania Tchad Thailand Timor-Leste Tjekkiet Togo Tokelau Tonga Trinidad og Tobago Tunesien Turkmenistan Turks- og Caicosøerne Tuvalu Tyrkiet Tyskland Uganda Ukraine Ungarn Uruguay USA Usbekistan Vanuatu Vatikanstaten Venezuela Vietnam Wallis og Futunaøerne Yemen Zambia Zimbabwe Ækvatorialguinea Østrig Ålandsøerne
Jeg vil gerne modtage Power BI-nyhedsbrevet. Erklæring om beskyttelse af personlige oplysninger.
Deltagelse kræver, at dine personlige oplysninger overføres til andre lande, som Microsoft driver forretning i, herunder USA. Når du sender denne formular, accepterer du overførslen af dine data uden for Kina. Erklæring om beskyttelse af personlige oplysninger.
Hvad er Power BI?
Power BI er en pakke med virksomhedsanalyseværktøjer til dataanalyse og vidensdeling. Overvåg din virksomhed, og få hurtigt svar med omfattende dashboards, der er tilgængelige på alle enheder.
Få de seneste nyheder fra Power BI-blog
Abonner via mail Du skal angive en gyldig mailadresse Du har angivet en personlig mailadresse. Angiv din arbejds- eller skolemailadresse. Noget gik galt. Prøv igen senere.
Beskyttelse af personlige oplysninger og cookies