Complete power BI assignment. Clear instructions
Data : https://databank.worldbank.org/source/world-development-indicators · Download Week 10 Dataset Homework · Upload all four .xlsx to Power BI · Change the default names for the datasets (Name them the way you want) · Clean the data (blanks, format, etc) · Imports of goods and services (current US$), GDP (current US$), Exports of goods and services (current US$) divide them by 1B. Change the column headers accordingly · Population, total divide by 1 M. Change the column header accordingly · Exact same thing for three other datasets…. a lot of work? There is a shortcut with M language which you can use · Go to the first dataset where you changed the values and renamed the columns , click on Home tab => Advanced editor , copy highlighted region below Open the next dataset => Home=> Advanced Editor=> Highlight the text the way I did on the next screenshot and press Ctrl+V You should get the following Do the same thing for all datasets Congrats you just saved 15 minutes! · Hit Close and Apply Model view: Connect datasets Data view : · Create a Table to store your measures. Add two folder “Summary” , “For 2015” · Create a calculated column “Date”, to show the year for each dataset accordingly. · Use Calendar(Date functions. · Apply the same steps for all datasets · Shortcut: Copy-Paste Syntax and just change the year for the new calculated columns · Measure, “Patents_per_Resident_ALL”. (put it into Summary folder) · Step 1 : Create a measure that sums up the number of patents per resident for 2015,2016,2017,2018 by country. In other words a total number of patents per resident for each country for 2015-2018 time period · Step 2: (same measure) This measure should not be filtered by any other columns except country. Add filter exception function to the measure, so whenever the report is getting filtered by specific columns it does not affect “Patents_per_Resident_ALL” measure. Measures below should be in “For 2015” folder · Measure, “Net Exporter” = formula Exports - Imports for 2015 · Measure, “Net Importer” = formula Imports – Exports for 2015 · Measure “Net Military” = · Step 1 calculate military budget in Bilions USD (% spend on military * GDP) · Calculate educational budget in the same way · Step 2 Military – Educational budget to get the final number for a measure · Tip: use variables · Measure “Net Education” = Shortcut: copy-paste syntax from previous measure, the only one thing you have to change is in Step 2 · Step 1 calculate military budget in Bilions USD (% spend on military * GDP) · Calculate educational budget in the same way · Step 2 Educational budget -Military to get the final number for a measure · Tip: use variables · Measure “Population without internet M” = it’s quite easy look at the columns you have. You may want to use divide function. · Measure “Labor force per sq kilometer” (same trick as with previous one, calculate,divide and sumx). Also, multiply the numerator by one million to convert population back · Measure “Population Growth M” = growth rate * population Report View · Create 2 Pages : “Four Years Summary”, “2015” · For 2015 , Create a Multi-Row card visual, Top 3 countries by GDP, use descending order · Same thing for Net Importer and Net Exporter ,Military, Education, Population growth rate M. To filter by top N use Net imports, Net exports,… etc · “Population without internet M” use clustered bar chart or any other visual you like. Filter by top 5 Population without internet · “Labor force per sq kilometer” use pie chart . Filter by top 5 Labor force per sq kilometer · Add a chart for patent application . No need to create a measure just create a visual. Filter by top 5 by patent application Homework Week 10 Cont. Measures Net Population Growth – you have to calculate what was the population growth rate for the four year period for each country. Tip: use following formula to calculate it ( 1 + Pop growth rate 2015) * ( 1 + Pop growth rate 2016) …. -1 GDP per Capita Avg = find the average gdp per person for each country over four year period (Gdp and Population columns) Tip: Keep in mind that Gdp is in billions, population is in millions. Variables are very helpful for this measure. There are multiple ways to solve this problem. Visuals Patents_per_Resident_All Table - do conditional formatting based on Population without internet measure that you created for 2015 Tab. Rename the column and add”/Internet availability” Turn off total column, make sure country names are easy to read (Tip : take a look at styles) . Use top 5 filter to get five countries with the most patents per resident Create a table for Net Population Growth – Filter on countries with growth rate over 8% or below 0%. Add Conditional Icons, red down arrow if growth below zero, green up arrow if the growth is above 0%.Add Land Area as well into the table, use the icon to reflect the size of the country (feel free to use the one you like) Create a table for GDP per Capita Avg – Filter on top 5 by GDP per Capita. Use color, icon or bar formatting to Create a Map - use military budget for 2015 as a size, use conditional formatting(color) , set the range min = -100 , mid point = 0, max = 100. Create new Page “World Bank Data Analytics” Use “buttons” to create links between the pages, you can also add text the way you see it in the Power Bi service , take a look at the button format pane. Data Countries/2015.xlsx Sheet1 CountryExports of goods and services (current US$)GDP (current US$)Government expenditure on education, total (% of GDP)Imports of goods and services (current US$)Individuals using the Internet (% of population)Inflation, consumer prices (annual %)Labor force participation rate, total (% of total population ages 15+) (national estimate)Land area (sq. km)Lending interest rate (%)Military expenditure (% of GDP)Patent applications, residentsPopulation growth (annual %)Population, totalUnemployment, total (% of total labor force) (national estimate) Austria2030000000003820000000005.45658016218800000000083.940141930.89656531660.720001228252000.69813815222051.12099250286426995.71999979 Bangladesh3382015415319500000000004828065135914.46.19428023013017011.709166671.350969906411.1172835651562562760 Chile716545208062440000000004.8753099447219011368576.629591954.34877353259.849998477435325.5149273231.8984872664431.177757471179693536.510000229 China2360000000000111000000000000200000000000050.31.43702451470.690002449424701.44.351.9104574069682520.50813674713712200000 Egypt, Arab Rep.4342318059332900000000007134770889537.8193834310.3704903446.9799995499545011.6251.7231474287182.2070386589244254713.05000019 Germany157000000000033600000000004.834980011132000000000087.589799350.5144205460.1699981734886001.101657717473840.865702644816866114.619999886 India41700000000021000000000000465000000000175.8724265950297319010.008333332.40512747125791.11663309113101524030 Israel945921701473000000000005.8673000348472519600477.35208966-0.63249001364.08999634216403.45940585.4969693912851.98128897683801005.25 Japan7730000000004390000000000079100000000091.058028390.7895178959.599998473645001.1428333330.95925133258839-0.1061249941271410003.400000095 Mexico40500000000011700000000005.23523998342800000000057.431042992.7206406559.7999992419439503.4391666670.46757183713641.2411777251218582584.309999943 Norway1460000000003860000000007.56933021512400000000096.810325692.17113665464.98000336365190.701.50727958111530.99508473751886074.300000191 Poland2350000000004780000000004.81226015122100000000067.99698716-0.87412587456.1599998530619002.1384566844676-0.066641101379864127.5 Portugal809515682821990000000004.8855400097947796788968.632861480.48793862458.5999984791605.601.787974411925-0.4141411021035807612.43999958 Spain40200000000012000000000004.28657007236600000000078.68963187-0.50046132158.8300018349966101.2708237832799-0.0775888624644483222.05999947 Sweden2210000000005050000000007.44390010820200000000090.61019664-0.0467847457240731001.06957913820381.05745468697991867.429999828 Turkey212000000000864000000000023000000000053.744979147.67085364851.2400016876963001.8223781153521.6660516827852940910.23999977 United Kingdom81000000000029300000000005.55615997385000000000092.000299870.36804684262.7524193001.859612024148670.792367506651162195.300000191 United States2270000000000182000000000000279000000000074.554202450.11862713662.6500015391474203.263.4778461682883350.7306411783206351635.28000021 Vietnam1730000000001930000000000172000000000450.63120090577.199996953100707.117505821.043962275926770761.850000024 Data Countries/2016.xlsx Sheet1 CountryExports of goods and services (current US$)GDP (current US$)Government expenditure on education, total (% of GDP)Imports of goods and services (current US$)Individuals using the Internet (% of population)Inflation, consumer prices (annual %)Labor force participation rate, total (% of total population ages 15+) (national estimate)Land area (sq. km)Lending interest rate (%)Military expenditure (% of GDP)Patent applications, residentsPopulation growth (annual %)Population, totalUnemployment, total (% of total labor force) (national estimate) Austria2070000000003960000000005.48435020419200000000084.323742570.89159236761.209999088252000.73254481720781.08139629987366686.010000229 Bangladesh368649976812210000000001.5355399854717157894318.025.51352572756.0900001513017010.409166671.372321482771.0913004821579708404.349999905 Chile704954620572500000000005.3420100216896974499583.558586023.78619355959.619998937435325.5883965771.9161559253861.325201606182090686.739999771 China2200000000000112000000000000194000000000053.2270.889999399424700.54.351.93003680812049810.54147851213786650000 Egypt, Arab Rep.3439263803733200000000006615950920241.2480670913.8136062146.6800003199545013.61.6666051529202.145225499444707212.40999985 Germany160000000000034700000000004.840219975134000000000084.165206640.49174862561.0099983234936001.146134456484800.807218539823486694.119999886 India44000000000022900000000000480000000000224.941026458029731909.67252.506470751131991.0898800813245095890 Israel956614041333190000000005.8571300519020156400579.65312418-0.5443886164.13999939216403.4201557445.47777813413001.96034896285460004.800000191 Japan80100000000049200000000003.18618011575200000000093.18272128-0.116666667603645001.044750.944046435260244-0.1152841771269945113.099999905 Mexico39900000000010800000000004.90949010842100000000059.5404462.82170784759.6800003119439504.7541666670.49579010413101.2032513291233333763.859999895 Norway1310000000003690000000008.03055000312300000000097.298203673.5564.51999664365145.7301.62668148812270.88096981552345194.679999828 Poland2450000000004730000000004.63922023822800000000073.3007041-0.66476733156.2200012230619001.9409363864261-0.042985131379700876.159999847 Portugal8294930880620600000000008058234657970.423567090.60739707558.4500007691605.602.004124282724-0.3154590171032545211.06999969 Spain41700000000012300000000004.22764015236800000000080.56133294-0.20267174158.5200004649956401.1377280427450.084430154648406219.62999916 Sweden2200000000005160000000007.61861991920400000000089.650947610.98426924572.0899963440731001.05283324720321.25645398599230856.989999771 Turkey201000000000870000000000022000000000058.347734017.77513415351.9700012276963002.0644994662301.6322506417982172410.84000015 United Kingdom76600000000026900000000005.42670011581000000000094.775800631.00841736862.9399986324193001.810780168138760.757874493656115934.809999943 United States2230000000000187000000000000274000000000085.544421291.26158320662.7900009291474203.5116666673.4189406012953270.7166694133229413114.869999886 Vietnam1920000000002050000000004.344810009187000000000532.6682481776.559997563100706.9605601.032286623936387241.850000024 Data Countries/2017.xlsx Sheet1 CountryExports of goods and services (current US$)GDP (current US$)Government expenditure on education, total (% of GDP)Imports of goods and services (current US$)Individuals using the Internet (% of population)Inflation, consumer prices (annual %)Labor force participation rate, total (% of total population ages 15+) (national estimate)Land area (sq. km)Lending interest rate (%)Military expenditure (% of GDP)Patent applications, residentsPopulation growth (annual %)Population, totalUnemployment, total (% of total labor force) (national estimate) Austria2260000000004170000000005.35803985621200000000087.935586592.08126858361.220001228252000.75617904220730.69462110487975665.5 Bangladesh3754681887825000000000005061116255405.70207015758.330001831301709.541.242917818611.0702440221596705934.369999886 Chile787901999452770000000005.419459827540101100182.327486932.18271846959.889999397435324.5528550791.930939934251.425185044184704396.960000038 China2420000000000123000000000000221000000000054.31.59313725509424700.84.351.89602959812457090.55912133113863950003.900000095 Egypt, Arab Rep.3728940217423600000000006908967391344.9502042529.5066083945.0099983299545018.1751.422031710252.090834729644259311.73999977 Germany174000000000036800000000004.905119896148000000000084.39415371.50949655861.2099990834937001.163609133477850.37372456826570023.75 India49800000000026500000000000582000000000322.490886999029731909.5083333332.509624558149611.06259739413386588350 Israel1020000000003530000000006.0926198969748544571981.581183590.24421052664216403.4973103015.52850637914361.93872567487133004.21999979 Japan86400000000048700000000003.18217992881900000000091.726547240.46721174760.53645000.9940.93330611260292-0.1644840361267857972.799999952 Mexico43600000000011600000000004.52281999645700000000063.852249096.0414572459.3400001519439507.33250.43949373613341.1639677791247773243.420000076 Norway1450000000003980000000007.91198015213100000000096.35760131.87510059663.72999954365122.9601.62227019511520.80767318252769684.159999847 Poland2850000000005270000000004.55956983626500000000075.985365952.07593553756.4399986330619001.89542726839240.012480099379748264.889999866 Portugal945735280882210000000005.0156102189233915880173.791213951.36861411658.9599990891605.601.683336736644-0.24388941103003008.869999886 Spain46100000000013100000000004.2077398341400000000084.60224571.95608333358.11000061499547.3501.23067912521670.2345879234659323617.21999931 Sweden2370000000005410000000007.56883001322300000000093.006266971.79449904772.7200012240731001.02407550219921.34744506100576986.71999979 Turkey224000000000859000000000025500000000064.6846176811.1443110852.7900009276963002.06525967281751.5910592098110189210.81999969 United Kingdom81000000000026700000000005.44209003484200000000090.424550492.55775577662.9099998524193001.766636791133010.679374474660588594.329999924 United States2370000000000195000000000000293000000000087.274889172.13011000462.8499984791474204.0966666673.3133852832939040.6310078933249855394.360000134 Vietnam227000000000224000000000022100000000058.143.52025688876.129997253100707.067505921.017796272945966421.870000005 Data Countries/2018.xlsx Sheet1 CountryExports of goods and services (current US$)GDP (current US$)Government expenditure on education, total (% of GDP)Imports of goods and services (current US$)Individuals using the Internet (% of population)Inflation, consumer prices (annual %)Labor force participation rate, total (% of total population ages 15+) (national estimate)Land area (sq. km)Lending interest rate (%)Military expenditure (% of GDP)Patent applications, residentsPopulation growth (annual %)Population, totalUnemployment, total (% of total labor force) (national estimate) Austria253000000000455000000000023900000000087.479137231.99838187761.369998938252000.74344624320390.48707192588405214.849999905 Bangladesh4056041146627400000000006424554618905.54362139501301709.6508333331.281519102691.0500446491613560390 Chile8525776380329800000000008579812155402.43488981459.840000157435324.178019251.8598062664061.391010541187291607.230000019 China2660000000000139000000000000255000000000002.07478890209424702.94.351.89634067613938150.45589967913927300003.799999952 Egypt, Arab Rep.4722566122725000000000007333145751346.9243367714.4014657843.3499984799545018.316666671.2499661969972.033262176984235959.819999695 Germany188000000000039600000000000163000000000087.037112091.73216766161.2799987834938001.178278719466170.300526702829057823.380000114 India5390000000002710000000000063900000000020.081300044.86069946747.9799995429731909.4541666672.38257161162891.0373233613526173285.329999924 Israel110000000000371000000000010800000000083.733156960.81485215163.93000031216403.4776064955.34483039915061.92662286588828004 Japan9180000000004950000000000090600000000091.281608490.9799036761.536450000.937282998253630-0.2026703451265291002.400000095 Mexico4800000000001220000000000050500000000065.772634484.89935015459.5499992419439508.040.48008351715551.1264211491261907883.279999971 Norway167000000000434000000000014200000000096.491657992.76483134564.12000275365107.8501.62830018710820.66009084453119163.799999952 Poland324000000000587000000000030700000000077.541734541.81295156556.3400001530617002.0552713284207-0.000200133379747503.849999905 Portugal105000000000242000000000010400000000074.660968150.99371568359.1100006191605.601.857473912661-0.160104021102838226.989999771 Spain5000000000001420000000000046100000000086.107235531.67506761557.91999817499603.47901.25607202815250.4379829944679775415.25 Sweden254000000000555000000000024100000000001.95353530172.9700012240731001.03110016618381.16164516101752146.360000134 Turkey243000000000778000000000024400000000071.0427610716.332463953.1599998576963002.54699564171561.4904448688231972410.89000034 United Kingdom8760000000002860000000000091600000000090.692008992.29283990363.0800018324193001.766023334128650.605929132664603444 United States2530000000000206000000000000314000000000088.498903172.44258329762.8699989391474204.9041666673.3162444582850950.5223373583266875013.900000095 Vietnam2600000000002450000000004.16744995125100000000069.847928683.53962805975.610000613100707.37187506460.992716375955403951.159999967