{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Score","Initial-Instructions","P(1)","P(2)"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":0,"defined_names":1,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Score","index":0,"n_rows":38,"n_cols":6,"rows_kept":38,"cut":false,"hidden":true,"merged":[]}
[]
[null,"Problem","Description","Possible Points","Earned Points","Note"]
[null,"1)","Import and Load:",{"$f":"100/ROWS($C$3:$C$32)","v":3.3333333333333335},{"$f":"D3","v":3.3333333333333335}]
[null,null,"Open & save a new Power BI Desktop file with the name \"YourNameTest05Problem01.pbix\".",3.3333333333333335,3.3333333333333335]
[null,null,"Be sure to include your actual name in the file name. Zero score if the file does not have your name.",3.3333333333333335,3.3333333333333335]
[null,null,"Use Power Query to import the three Excel Tables from the \"YourNameBusn218Test05.xlsm\" Excel file.",3.3333333333333335,3.3333333333333335]
[null,null,"Create Relationships",3.3333333333333335,3.3333333333333335]
[null,null,"Create these two Measures:",3.3333333333333335,3.3333333333333335]
[null,null,"Total Sales ($) = SUMX(fUnitsPBID,RELATED(dProductsPBID[RetailPrice])*fUnitsPBID[UnitsSold])",3.3333333333333335,3.3333333333333335]
[null,null,"Total Units = SUM(fUnitsPBID[UnitsSold])",3.3333333333333335,3.3333333333333335]
[null,null,"Create Visuals:",3.3333333333333335,3.3333333333333335]
[null,null,"Use a Matrix visual for the cross tabulated report with Customer field in the Rows area & Product field in the Columns area.",3.3333333333333335,3.3333333333333335]
[null,null,"To sort a column in a Matrix visual, click the column header.",3.3333333333333335,3.3333333333333335]
[null,null,"Use a clustered bar or column chart for your visual with Customer field in the Axis area & Product field in the Legend area.",3.3333333333333335,3.3333333333333335]
[null,null,"To sort a bars or columns in a visual, hover your cursor over the upper right corner of the visual to find sort option.",3.3333333333333335,3.3333333333333335]
[null,null,"You can add a Text Box from the Insert group in the Home tab of the Ribbon to create a title for the Matrix.",3.3333333333333335,3.3333333333333335]
[null,null,"Upload to Power BI Online:",3.3333333333333335,3.3333333333333335]
[null,null,"When you are done with the Power BI Desktop file, save it, close it, then Publish it to Power BI Online to the Fall2022Busn218 Workspace.",3.3333333333333335,3.3333333333333335]
[null,null,"Other 1",3.3333333333333335,3.3333333333333335]
[null,null,"Other 2",3.3333333333333335,3.3333333333333335]
[null,"2)","Did you use Power Query to connect to the SQL boomdata Database?",3.3333333333333335,3.3333333333333335]
[null,null,"Did you import the fact table only?",3.3333333333333335,3.3333333333333335]
[null,null,"In the Power Query Editor, did you remove all fields except \"Website\" and \"Quantity\"?",3.3333333333333335,3.3333333333333335]
[null,null,"Load it to the Power Pivot Data Model. ",3.3333333333333335,3.3333333333333335]
[null,null,"Did you select both \"Only Create Connection\" and \"Add data to Data Model\" in the Import Data dialog box?",3.3333333333333335,3.3333333333333335]
[null,null,null,3.3333333333333335,3.3333333333333335]
[null,null,"Did you create the one Measure: TotalUnits:=SUM(fTransactions[Quantity])",3.3333333333333335,3.3333333333333335]
[null,null,"Did you create the simple Data Model PivotTable \"Units by Web Site\"?",3.3333333333333335,3.3333333333333335]
[null,null,"Did you save this test file and then upload it to the Test #5 Upload link in the Home Area of Canvas.",3.3333333333333335,3.3333333333333335]
[null,null,"Other 1",3.3333333333333335,3.3333333333333335]
[null,null,"Other 2",3.3333333333333335,3.3333333333333335]
[null,null,"Other 3",3.3333333333333335,3.3333333333333335]
[null,null,"Total Possible Points",{"$f":"SUM(D3:D32)","v":99.999999999999972}]
[null,null,"Is Test Submitted Past due date-time?","No",{"$f":"IF(D34=\"Yes\",-0.25*SUM(E3:E32),\"\")","v":""},{"$f":"IF(D34=\"Yes\",\"Deduction for late test\",\"\")","v":""}]
[null,null,null,"Your Earned Test Score:",{"$f":"SUM(E3:E34)","v":99.999999999999972}]
[]
[]
[null,"Be sure to scroll up to see whole score sheet.",null,null,null,"fall 2022"]

{"sheet":"Initial-Instructions","index":1,"n_rows":14,"n_cols":6,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
["* This test covers everything in class up to minute marks 02:07:06 to 03:44:07 in the MECS class video #4"]
[null,"Test Instructions:"]
["1)","You must rename each test file so that the file name contains your name."]
[null,"For example, if your name is “Sioux Noline” and the test file has the name “Busn218Fall2022Test05.xlsm\","]
[null,"you MUST rename the file to become: “SiouxNoline Busn218Fall2022Test05.xlsm”."]
[null,"Tests that do not follow this naming rule are scored as zero."]
["2)","The Excel test file MUST use the file extension \".xlsm\". Tests that do not follow this rule are scored as zero."]
["3)","This test has two problem on the worksheets with the name \"P(1)\" and \"P(2)\"."]
["4)","Late tests without a documentable emergency are assessed as a 25% deduction."]
[null,"You can hand in tests late if a documentable emergency occurs, like documented deaths or medical emergencies."]
[5,"The test scores earned will count toward your grade for the class."]
[]
["If you have questions you can e-mail me at mgirvin@highline.edu"]
[null,null,null,null,null,"Fall, 2022 File"]

{"sheet":"P(1)","index":2,"n_rows":152,"n_cols":15,"rows_kept":152,"cut":false,"hidden":false,"merged":[]}
[]
["Goals:","Source Data: Excel Tables on this worksheet."]
[null,"Data analysis goal:  Using Power BI Desktop, create a total sales by customer and product cross tabulated report and visualization, as shown to the right =>"]
[null,"Tools to use: Power BI Desktop Data Model and Visualizations."]
["Hints:","Open & save a new Power BI Desktop file with the name \"YourNameTest05Problem01.pbix\"."]
[null,"Be sure to include your actual name in the file name. Zero score if the file does not have your name."]
[null,"Save & close this Excel file, then import the data from this worksheet into your Power BI Desktop file."]
[null,"Build your solution in the Power BI Desktop file. A picture of the finished data model is shown to the right =>"]
[null,"Create these two Measures:"]
[null,"Total Sales ($) = SUMX(fUnitsPBID,RELATED(dProductsPBID[RetailPrice])*fUnitsPBID[UnitsSold])"]
[null,"Total Units = SUM(fUnitsPBID[UnitsSold])"]
[null,"Use a Matrix visual for the cross tabulated report with Customer field in the Rows area & Product field in the Columns area."]
[null,"To sort a column in a Matrix visual, click the column header."]
[null,"Use a clustered bar or column chart for your visual with Customer field in the Axis area & Product field in the Legend area."]
[null,"To sort a bars or columns in a visual, hover your cursor over the upper right corner of the visual to find sort option."]
[null,"You can add a Text Box from the Insert group in the Home tab of the Ribbon to create a title for the Matrix."]
[null,"When you are done with the Power BI Desktop file, save it, close it, then Publish it to Power BI Online to the Fall2022Busn218 Workspace."]
["The MECS video #4, after the 02:12:07 and all the way to the end of the video, shows how to complete a Power BI project like this. right =>",null,null,null,null,null,null,null,null,null,null,null,null,null,"https://www.youtube.com/watch?v=TjSnQ4VDHTE&list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW&index=7&t=7927s"]
["Do not copy and paste work previously done."]
["Do not ask for help with test or work with others on test."]
["It is OK to re-watch class videos, read pdf notes, look at the Excel files for this class, or e-mail Michael Girvin at mgirvin@highline.edu with questions."]
[]
[]
[null,"Fact Table = fUnitsPBID",null,null,null,"Dimension Table = dProductsPBID"]
[]
[null,"ProductID","CustomerID","UnitsSold",null,"ProductID","Product","FlightRange(M)","RetailPrice"]
[null,"1503-YI",2185,94,null,"1503-YI","Yanaki",35,27.95]
[null,"6816-BN",2255,22,null,"6816-BN","Bellen",25,26.95]
[null,"6816-BN",2045,67,null,"9720-CA","Carlota",20,31.95]
[null,"3414-AN",2010,21,null,"3414-AN","Aspen",40,37.950000000000003]
[null,"1503-YI",2080,13,null,"5018-SE","Sunshine",35,19.95]
[null,"6816-BN",2150,16]
[null,"3414-AN",2220,123,null,"Dimension Table = dCustomerPBID"]
[null,"6816-BN",2185,64]
[null,"6816-BN",2080,71,null,"CustomerID","CustomerName","Email","Contact"]
[null,"6816-BN",2220,120,null,2010,"Kite Flight",{"$f":"LOWER(dCustomerPBID[[#This Row],[Contact]])&\"@\"&LOWER(SUBSTITUTE(dCustomerPBID[[#This Row],[CustomerName]],\" \",\"\"))&\".com\"","v":"han@kiteflight.com"},"Han"]
[null,"6816-BN",2255,12,null,2045,"Fred Myer",{"$f":"LOWER(dCustomerPBID[[#This Row],[Contact]])&\"@\"&LOWER(SUBSTITUTE(dCustomerPBID[[#This Row],[CustomerName]],\" \",\"\"))&\".com\"","v":"tim@fredmyer.com"},"Tim"]
[null,"3414-AN",2080,69,null,2080,"Great Winds",{"$f":"LOWER(dCustomerPBID[[#This Row],[Contact]])&\"@\"&LOWER(SUBSTITUTE(dCustomerPBID[[#This Row],[CustomerName]],\" \",\"\"))&\".com\"","v":"sonia@greatwinds.com"},"Sonia"]
[null,"9720-CA",2220,25,null,2115,"Flying Toys",{"$f":"LOWER(dCustomerPBID[[#This Row],[Contact]])&\"@\"&LOWER(SUBSTITUTE(dCustomerPBID[[#This Row],[CustomerName]],\" \",\"\"))&\".com\"","v":"chantel@flyingtoys.com"},"Chantel"]
[null,"6816-BN",2220,24,null,2150,"Boomerang Man",{"$f":"LOWER(dCustomerPBID[[#This Row],[Contact]])&\"@\"&LOWER(SUBSTITUTE(dCustomerPBID[[#This Row],[CustomerName]],\" \",\"\"))&\".com\"","v":"rich@boomerangman.com"},"Rich"]
[null,"6816-BN",2045,49,null,2185,"Bailey Boomerangs",{"$f":"LOWER(dCustomerPBID[[#This Row],[Contact]])&\"@\"&LOWER(SUBSTITUTE(dCustomerPBID[[#This Row],[CustomerName]],\" \",\"\"))&\".com\"","v":"sioux@baileyboomerangs.com"},"Sioux"]
[null,"9720-CA",2220,32,null,2220,"Top Ten Toys",{"$f":"LOWER(dCustomerPBID[[#This Row],[Contact]])&\"@\"&LOWER(SUBSTITUTE(dCustomerPBID[[#This Row],[CustomerName]],\" \",\"\"))&\".com\"","v":"miki@toptentoys.com"},"Miki"]
[null,"3414-AN",2010,52,null,2255,"Flying High",{"$f":"LOWER(dCustomerPBID[[#This Row],[Contact]])&\"@\"&LOWER(SUBSTITUTE(dCustomerPBID[[#This Row],[CustomerName]],\" \",\"\"))&\".com\"","v":"gigi@flyinghigh.com"},"Gigi"]
[null,"1503-YI",2220,53]
[null,"1503-YI",2045,53]
[null,"1503-YI",2150,31]
[null,"1503-YI",2185,71]
[null,"1503-YI",2150,73]
[null,"1503-YI",2220,25]
[null,"3414-AN",2150,5]
[null,"3414-AN",2045,121]
[null,"9720-CA",2185,49]
[null,"6816-BN",2150,30]
[null,"6816-BN",2220,56]
[null,"1503-YI",2010,60]
[null,"1503-YI",2080,34]
[null,"9720-CA",2010,27]
[null,"1503-YI",2150,20]
[null,"1503-YI",2010,77]
[null,"3414-AN",2045,33]
[null,"1503-YI",2150,40]
[null,"3414-AN",2150,16]
[null,"9720-CA",2080,58]
[null,"6816-BN",2080,32]
[null,"6816-BN",2080,24]
[null,"9720-CA",2115,35]
[null,"9720-CA",2255,12]
[null,"9720-CA",2255,30]
[null,"6816-BN",2150,35]
[null,"6816-BN",2115,5]
[null,"6816-BN",2185,61]
[null,"9720-CA",2115,26]
[null,"3414-AN",2115,51]
[null,"9720-CA",2045,12]
[null,"6816-BN",2150,115]
[null,"1503-YI",2185,30]
[null,"6816-BN",2115,21]
[null,"1503-YI",2080,25]
[null,"9720-CA",2185,67]
[null,"1503-YI",2010,60]
[null,"1503-YI",2185,29]
[null,"1503-YI",2220,28]
[null,"6816-BN",2010,59]
[null,"1503-YI",2115,94]
[null,"1503-YI",2010,62]
[null,"9720-CA",2255,35]
[null,"1503-YI",2255,48]
[null,"3414-AN",2220,5]
[null,"1503-YI",2080,26]
[null,"9720-CA",2255,75]
[null,"3414-AN",2010,103]
[null,"1503-YI",2150,21]
[null,"3414-AN",2080,58]
[null,"9720-CA",2080,17]
[null,"3414-AN",2220,8]
[null,"3414-AN",2080,46]
[null,"3414-AN",2255,70]
[null,"3414-AN",2080,90]
[null,"6816-BN",2080,34]
[null,"6816-BN",2185,83]
[null,"6816-BN",2150,15]
[null,"1503-YI",2045,47]
[null,"9720-CA",2115,13]
[null,"3414-AN",2220,19]
[null,"9720-CA",2185,7]
[null,"6816-BN",2115,59]
[null,"3414-AN",2255,34]
[null,"6816-BN",2080,9]
[null,"6816-BN",2220,53]
[null,"6816-BN",2010,23]
[null,"9720-CA",2115,34]
[null,"6816-BN",2115,35]
[null,"3414-AN",2080,48]
[null,"6816-BN",2115,24]
[null,"1503-YI",2080,13]
[null,"3414-AN",2255,111]
[null,"6816-BN",2150,43]
[null,"3414-AN",2185,44]
[null,"1503-YI",2220,124]
[null,"9720-CA",2045,21]
[null,"6816-BN",2045,61]
[null,"9720-CA",2185,16]
[null,"1503-YI",2115,45]
[null,"6816-BN",2115,59]
[null,"1503-YI",2185,23]
[null,"9720-CA",2045,30]
[null,"9720-CA",2185,14]
[null,"9720-CA",2150,107]
[null,"3414-AN",2080,37]
[null,"6816-BN",2045,86]
[null,"3414-AN",2150,24]
[null,"6816-BN",2220,59]
[null,"1503-YI",2115,48]
[null,"3414-AN",2185,12]
[null,"9720-CA",2045,97]
[null,"9720-CA",2255,108]
[null,"3414-AN",2045,22]
[null,"3414-AN",2255,1]
[null,"6816-BN",2080,22]
[null,"9720-CA",2185,27]
[null,"9720-CA",2045,6]
[null,"9720-CA",2220,82]
[null,"1503-YI",2080,102]
[null,"3414-AN",2080,15]
[null,"6816-BN",2185,119]
[null,"3414-AN",2115,43]
[null,"1503-YI",2255,19]
[null,"6816-BN",2185,33]
[null,"1503-YI",2045,34]
[null,"9720-CA",2185,29]
[null,"3414-AN",2045,40]
[null,"9720-CA",2010,28]

{"sheet":"P(2)","index":3,"n_rows":31,"n_cols":10,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Goal: Import a fact table from an SQL database and create a simple report that shows total units sold for each web site"]
[null,"Suggested steps to complete this problem:"]
[null,"1) In this test file, use Power Query to connect to the SQL boomdata Database using these data credentials:"]
[null,"Server:","pond.highline.edu"]
[null,"Database:","boomdata"]
[null,"UserName:","excelisfun"]
[null,"Password:","ExcelIsFun!"]
[]
[null,"2) Save this file often, for example: save after the Power Query step, save after creating the Measure, save after creating the report."]
[null,"3) Import the fact table only. The table name is fTransactions. In the Power Query Editor, remove all fields except \"Website\" and \"Quantity\""]
[null,"4) Load it to the Power Pivot Data Model. Be sure to select both \"Only Create Connection\" and \"Add data to Data Model\" in the Import Data dialog box."]
[null,"5) Create the one Measure: TotalUnits:=SUM(fTransactions[Quantity])"]
[null,"6) Create the simple Data Model PivotTable report as shown to the right =>"]
[null,"7) Save this test file and then upload it to the Test #5 Upload link in the Home Area of Canvas."]
["MECS video #4 at the 03:02:53 minute mark shows how to connect to an SQL database. Link to right =>",null,null,null,null,null,null,null,"https://www.youtube.com/watch?v=TjSnQ4VDHTE&list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW&index=8&t=10973s"]
["Do not copy and paste work previously done."]
["Do not ask for help with test or work with others on test."]
["It is OK to re-watch class videos, read pdf notes, look at the Excel files for this class, or e-mail Michael Girvin at mgirvin@highline.edu with questions.",null,null,null,null,null,null,null,null,"Hint for the Password: E x c e l I s F u n"]
[]
[null,null,null,null,null,null,null,null,null,"E = Capital \"E\""]
[null,null,null,null,null,null,null,null,null,"x = lowercase \"x\""]
[null,null,null,null,null,null,null,null,null,"c = lowercase \"c\""]
[null,null,null,null,null,null,null,null,null,"e = lowercase \"e\""]
[null,null,null,null,null,null,null,null,null,"l = lowercase \"l\" = the letter after \"k\""]
[null,null,null,null,null,null,null,null,null,"I = Capital \"I\" = the letter after \"h\""]
[null,null,null,null,null,null,null,null,null,"s = lowercase \"s\""]
[null,null,null,null,null,null,null,null,null,"F = Capital \"F\""]
[null,null,null,null,null,null,null,null,null,"u = lowercase \"u\""]
[null,null,null,null,null,null,null,null,null,"n = lowercase \"n\""]
[null,null,null,null,null,null,null,null,null,"! = punctuation = \"!\" = exclamation point"]