POWERBI FROM DATA TO DASHBOARD GALWAY POWERBI MEETUP

POWERBI – FROM DATA TO DASHBOARD GALWAY POWERBI MEETUP GROUP

BIT ABOUT ME • Anne Walsh – freelance trainer AKA The Excel Lady • Based in Galway • Author of “Your Excel survival kit – your guide to surviving and thriving in an Excel world” • www. the-excel-expert. com and www. theexcellady. com

RAW FLAT DATA FILE Taken from Kaggle Data on medical appointments in South America https: //www. kaggle. com/joniarro ba/noshowappointments/home Finished Dashboard

STEP 1 - WHAT DO I WANT ON DASHBOARD • Total number of patients • Total number of appointments • Representation of neighbourhoods with highest Did Attend and DNA • View by Gender • View by SMS Received • Filter by time • Use a KPI to view

STEP 2 – DATA PREPARATION • Replace 0 and 1 in No. Show with Attend and DNA • Replace 0 and 1 in SMS Received with Yes and No. • Normalise – yes or no – how to set that up – for Gender, and Neighbourhood • Create a calendar – dynamic • Add a table to show KPI • Check relationships created • Link to champagne tower

STEP 2 (A) – DYNAMIC CALENDAR • Thanks to Ken Puls of Academy. Power. Query for this technique • Create two blank queries: – Start Date (extracting the earliest date in the data) – Last. Date (extracting the latest date in the data) – Create a Calendar query • Start with Blank Query • Enter this formula(EXACTLY) : {Number. From(Start_Date). . Number. From(End_Date)} • Convert to Table, • Convert to Date Format and rename as Date

STEP 3 – CALCULATE MEASURES Prepare the following measures: • Total DNA (Total DNA) • Total Appointments (Total Appointments) • % of Attend of Appointments (Total Attend/ Total Appointments • Total Patients (Total Patients) • % of DNA of Appointments (Total DNA/ Total Appointments) • Total Attend (Total Attend)

STEP 4 – CREATE VISUALS • Matrix: • KPI Card/Gauge • % of Attend of Appointments (Total Attend/ Total Appointments • Where target met and where not • % of DNA of Appointments (Total DNA/ Total Appointments) • View by Gender • Cards • Total Appointments (Total Appointments) • Total Patients (Total Patients) • Slicers • View by Time • View by SMS options

FINISHED OUTPUT Final output

CONCLUSION Overview 1. Decide what you want 2. Prepare your data 3. Prepare your measures 4. Do your visuals Files and presentation will be uploaded to Galway Power. BI group.