ISYS $284$ Analysis$1$ Queries Assignment

Description

Universal Pictures is one of Hollywood$$s $$big six$$ movie studios, and they are considering making a

blockbuster movie for the $2023$ summer season. Universal$$s Marketing department has hired you to

identify celebrities they should hire for the proposed movie.

The movie studio has provided you with the following data regarding the celebrities: how much

salary would each celebrity demand for the movie, their TV$/$Radio rankings, their press rankings,

their marketability rankings and their social ranking. All rankings range between $1($highest

ranked$)$ and $100($lowest ranked$).$ Universal Pictures$$ aim is to be able to hire celebrities with

higher rankings $($i$.$e$.,$ lower numerical values$)$ but spend less on salaries $($i$.$e$.,$ lower salaries$).$

The data is given to you in $3$ separate worksheets $($Celebrities Data$1,$ Celebrities Data$2,$ Salary

Data$).$ Your task is to combine them into a single table and provide Universal Pictures with answers

to the following questions. You should create a query in Power BI for each of the questions below

labeled with question number $($e$.$g$.$ Q$1$a$,$ Q$1$b$,$ Q$2).$ Before submitting the Power BI file, make sure to

apply all the queries. No visualizations are needed.

$1.$ a$)$ Which male celebrity asks for the highest salary? b$)$ Which female celebrity asks for

the highest salary? $($Q$1$a and Q$1$b queries each should only show a single row that has

the information of the male$/$female celebrity that has the highest salary$)(10$pts$)$

$2.$ Calculate a measure for each celebrity called $$overall popularity ranking$$ that factors in

each ranking with the weightings below:

TV$/$Radio: $40\%$

Press: $20\%$

Marketability: $30\%$

Social: $10\%$

Which celebrity is most popular based on this measure? $($Remember: $1$ is the highest

ranking showing the most popular, $100$ is the lowest ranking showing the least popular$)$

$($Q$2$ should show a single row that has the information of the celebrity that is most

popular based on the measure. Hint: use a custom column to create an equation that

multiplies each rating with corresponding weight and sums them$)(10$pts$)$

$3.$ a$)$ What is the average salary celebrities ask for? b$)$ How many celebrities ask for an

above average salary? $($Q$3$a and Q$3$b should show a single number that display the

answer of the corresponding question$)(10$pts$)$

$4.$ What is the average salary male celebrities ask for? How many male celebrities ask for

a below average salary? $($Q$4$a and Q$4$b should show a single number that display the

answer of the corresponding question$)(10$pts$)$

$5.$ Is there a difference between the average marketability rank of different category level

celebrities $($i$.$e$.$ A$-$level, B$-$level, C$-$level$)?($Q$5$ should show average marketability rank of

different category levels$)(10$pts$)$

$6.$ Is there a difference between the average female and average male celebrity salaries?

$($Q$6$ should show average female celebrity salary and average male celebrity

salary$)(10$pts$)$

Assignment Deliverable

Your deliverable for this assignment will be:

$$ Power BI file $($LastNameFirstInitial$.$pbix$),$ which shows your applied queries.

Name Gender Category TV$/$Radio Rank Press Rank Marketability Rank Social Rank

Adam Sandler Male A$-$level $4457413$

Alec Baldwin Male A$-$level $24395076$

Amy Poehler Female B$-$level $72723281$

Angelina Jolie Female A$-$level $14203275$

Ashton Kutcher Male B$-$level $69601918$

Ben Affleck Male A$-$level $812459$

Ben Roethlisberger Male B$-$level $18389578$

Beyonce Knowles Female A$-$level $15255$

Blake Lively Female A$-$level $29226826$

Bon Jovi Male B$-$level $35479917$

Calvin Harris Male B$-$level $64889037$

Carrie Underwood Female B$-$level $85641428$

Channing Tatum Male A$-$level $84413640$

Charlie Sheen Male B$-$level $28453731$

Charlize Theron Female A$-$level $39531487$

Chris Martin Male B$-$level $80374111$

Christopher Nolan Male B$-$level $46428194$

Danica Patrick Female C$-$level $85745374$

David Guetta Male B$-$level $9970768$

David Letterman Male C$-$level $17354192$

Draw Berrymore Female A$-$level $778926$

Dwayne Johnson Male B$-$level $8949427$

E$.$L$.$ James Female C$-$level $375110079$

Ellen DeGeneres Female B$-$level $3146549$

Emma Stone Female A$-$level $37432597$

Emma Watson Female A$-$level $126420$

Gisele Bundchen Female B$-$level $55798065$

Gwyneth Paltrow Female A$-$level $76401470$

Halle Berry Female A$-$level $61848542$

Heidi Klum Female B$-$level $56806430$

Howard Stern Male C$-$level $82959373$

Hugh Jackman Male A$-$level $32277632$

J$.$K$.$ Rowling Female C$-$level $89503758$

James Patterson Male C$-$level $59687857$

Jay$-$Z Male A$-$level $3147166$

Jennifer Aniston Female A$-$level $66589097$

Jennifer Hudson Female A$-$level $79198810$

Jennifer Lawrence Female A$-$level $2021322$

Jennifer Lopez Female A$-$level $21152151$

Jerry Bruckheimer Male C$-$level $60997385$

Jerry Seinfeld Male A$-$level $89912144$

Jon Stewart Male C$-$level $11593914$

Joss Whedon Male A$-$level $95828286$

Julia Roberts Female A$-$level $2687171$

Justin Bieber Male A$-$level $44961$

Kanye West Male A$-$level $25292515$

Katy Perry Female A$-$level $23$