Procuriamoci anzitutto il necessario per poter lavorare, in questo esempio utilizzerò un database MySQL, un file Excel con ulteriori dati da integrare e Power BI.
In ordine avremo quindi bisogno di:
- XAMPP per server MySQL e PHPMyAdmin (non è necessario utilizzare XAMPP, si può usare un qualunque database MySQL ovviamente)
- Un database con dei dati di esempio che puoi scaricare da qui (questo database è già stato utilizzato nell’esercizio [mysql] Creare tabella pivot dinamica in MySQL (per esordienti totali))
- Un file excel con integrazioni dei dati per clienti e agenti che puoi scaricare da qui
- Power BI che possiamo ottenere gratuitamente a questo link
- MySQL Connector/NET 8.0.20 che si può scaricare da questo link (se non lo installiamo in anticipo sarà Power BI stesso ad invitarci di farlo nel momento in cui vorremo collegare un database MySQL)
Preparato tutto questo possiamo cominciare a lavorare con Power BI.
1. Importazione dati da MySQL
Avviamo Power BI e scegliamo Recupera dati dalla maschera iniziale.
Nel campo di ricerca cerchiamo mysql:
Selezioniamo il database MySQL. Se non abbiamo installato il connettore, come indicato prima, ci verrà chiesta l’installazione. In caso contrario procediamo configurando il database. Inseriamo, se stiamo usando XAMPP, come server 127.0.0.1 e il nome del database, nel mio caso powerbi.
Procediamo premendo OK ed impostiamo nome utente del database come root e lasciamo il campo password vuoto.
All’avviso sul supporto crittografia premiamo OK.
A questo punto selezioniamo tutte le tabelle che intendiamo caricare. Nel caso specifico selezioneremo tutte le tabelle eccetto storico_vendite.
Premiamo sul tasto carica.
2. Elaborazione dati
A questo punto elaboriamo le connessioni tra le tabelle. Sulla sinistra spostiamoci su modello:
Dovremmo vedere qualcosa di simile a questo.
Spostandoci su una qualsiasi delle relazioni notiamo come siano stati associati automaticamente gli ID. Purtroppo queste associazioni non vanno bene, perché i nomi delle colonne, tra le varie tabelle non corrispondono. A vendite.idAgente corrisponde infatti agenti.id, mentre secondo il sistema automatico vendite.id corrisponde a agenti.id. Possiamo ricostruire le associazioni trascinando i campi gli uni sugli altri, oppure andando ad intervenire da Gestisci relazioni, nella scheda Home > Relazioni.
Cliccando su Gestisci relazioni si aprirà una schermata come la seguente:
Modifichiamo opportunamente tutte le relazioni. Per esempio quella tra vendite ed agenti nel modo seguente:
Se abbiamo fatto tutto correttamente dovremmo vedere qualcosa di simile a questo:
Faccio notare che in tutti i casi ho impostato direzione filtro incrociato su Entrambi. L’utilizzo dell’opzione è finalizzato specificatamente alla semplificazione dell’uso dei filtri. La stessa Microsoft lo spiega nell’articolo Abilitare il filtro incrociato bidirezionale con DirectQuery in Power BI Desktop
Con il filtro incrociato bidirezionale, gli autori di report e modelli di dati hanno ora un maggiore controllo sulla modalità di applicazione dei filtri durante l’uso di tabelle correlate. Il filtro incrociato bidirezionale consente loro di applicare filtri su entrambi i lati di una relazione tra tabelle. Per ottenere questo risultato, il contesto di filtro viene propagato a una seconda tabella correlata sull’altro lato della relazione.
Fatto tutto questo lo schema che visualizzeremo sarà come il seguente:
3. Creiamo la nostra prima tabella nel report
Adesso mettiamo alla prova il sistema creato e proviamo a visualizzare il totale venduto dagli agenti.
Modifichiamo anzitutto il nome della pagina in Vendite:
Dalle visualizzazioni selezioniamo Tabella:
Poi afferriamo il nome dell’agente e trasciniamolo nella tabella. Stessa cosa per importo dalla tabella vendite:
Vedremo un risultato simile a questo:
Questo succede perché nelle vendite c’è un idAgente che non corrisponde ad alcun agente nella tabella agenti. Quello che vorremmo ottenere è una tabella di agenti LEFT JOIN vendite. Per farlo andiamo su Query e clicchiamo su Trasforma dati.
Clicchiamo col destro nel riquadro Query e selezioniamo Nuova query > Combina > Unisci query come nuova:
Configuriamo una LEFT OUTER JOIN nella maniera seguente:
A questo punto vedremo una tabella come la seguente:
Noi vogliamo visualizzare i dati aggregati delle vendite, quindi clicchiamo sul pulsante in alto a destra di powerbi vendite.
Selezioniamo l’opzione di aggregazione nella maniera seguente, scegliendo quantità e importo.
Dovremmo ottenere qualcosa di simile a questo:
Sulla destra clicchiamo sulla nuova tabella col destro e rinominiamola in vendite_x_agente. A titolo di esempio vogliamo anche aggiungere una colonna che calcoli l’importo medio di vendita per quantità di venduto.
Creiamo la colonna nella maniera seguente:
La formula che usiamo è: = Table.AddColumn(#"Colonne powerbi vendite aggregate", "media", each [Somma di powerbi vendite.importo] / [Somma di powerbi vendite.qta])
Fatto tutto questo salviamo e scegliamo di applicare i dati dal prompt che ci apparirà sullo schermo. Chiudiamo e torniamo a Power BI.
Sotto la voce Campi clicchiamo col destro su vendite_x_agente e aggiungiamo un’altra colonna (lo potevamo fare anche prima, ma per esercizio voglio farlo da questa posizione diversa): Nel campo formula digitiamo: nominativo = vendite_x_agente[nome]& " " & vendite_x_agente[cognome]
In questo modo abbiamo concatenato il nome e il cognome di ciascun agente aggiungendo in mezzo uno spazio.
A questo punto rinominiamo le colonne aggiunte prima, in modo da ottenere la seguente situazione:
Reimpostiamo la tabella nella maniera seguente:
4. Integriamo i dati da un foglio Excel
Adesso procediamo integrando i dati da un foglio Excel, per calcolare le provvigioni per ciascun agente.
Importiamo anzitutto i dati andando su Home > Dati > Recupera dati. Selezioniamo Excel. (c’è anche il collegamento veloce sotto Dati)
Selezioniamo il file excel anagrafica_clienti.xlsx.
Selezioniamo entrambe le tabelle e clicchiamo su Carica.
Andando su Modello vedremo che Power BI avrà collegato tutte le tabelle totalmente a caso.
Clicchiamo col destro su una delle due, poi andiamo su Gestisci relazioni, usando SHIFT e il mouse selezioniamo le relazioni che interessano Compensi agenti e Anagrafica Clienti (nella maniera mostrata nell’immagine) e clicchiamo su Elimina per tutte le relazioni selezionate (e create).
Ricreiamo le relazioni nella maniera seguente:
Fatto questo torniamo sulle tabelle e andiamo su vendite_x_agenti, dove abbiamo aggiunto prima i dati di vendita.
Clicchiamo col destro su vendite_x_agenti e selezioniamo Modifica query. Dalla finestra di modifica andiamo su Home > Combina > Merge query.
Impostiamo l’unione nella maniera seguente:
Clicchiamo sul pulsante in alto a destra della nuova colonna aggregata:
Selezioniamo Compenso e Fisso nella maniera seguente:
Salviamo il tutto e chiudiamo. Clicchiamo col destro sulla tabella e aggiungiamo una Nuova colonna. Inseriamo la seguente formula:
provvigione = vendite_x_agente[totale_importo]*vendite_x_agente[Compensi agenti.Compenso]
In questo modo calcoliamo il compenso in percentuale sul totale venduto. Torniamo sul report e aggiungiamo alla tabella anche la provvigione. Dovremo arrivare a vedere qualcosa di simile:
5. Formattazione valori
A questo punto vogliamo formattare i valori delle vendite nel formato valuta. Essendo dati aggregati in una tabella generata da JOIN, possiamo anzitutto formattare i valori all’origine, nelle tabelle di partenza. Questo vuol dire che ci spostiamo su vendite.
Selezioniamo la colonna desiderata ed inseriamo il formato Valuta. Possiamo modificare il tipo di valuta cliccando sull’opportuno pulsante come nell’immagine precedente.
Spostiamoci adesso su vendite_x_agente. Clicchiamo sulla colonna totale_importo, da Tipo dati selezioniamo Numero decimale, da Formato selezioniamo Valuta come prima. Stesso discorso per la provvigione.
Fatto questo possiamo tornare sulla visualizzazione dei dati. Dove vedremo qualcosa di simile a questo:
6. Creazione di Drill-through
Adesso vogliamo creare una analisi dettagliata per il totale venduto da ogni agente.
Aggiungiamo anzitutto una seconda pagina che chiameremo VENDITE AGENTE
Aggiungiamo una scheda per il titolo dell’agente analizzato:
Impostiamo una scheda che mostri anche il totale venduto nella maniera seguente:
Adesso aggiungiamo l’importo del venduto per ciascun agente per ciascuna città, procedendo nella maniera seguente:
Fatto questo attiviamo il campo Drill-thorugh sul nominativo dell’agente. Per farlo procediamo nel modo seguente:
Fatto tutto questo andiamo sulla pagina VENDITE. Clicchiamo col destro su un nominativo agente e scegliamo Drill-thought > VENDITE AGENTE.
Visualizzeremo i dati dell’agente nel report VENDITE AGENTE creato in precedenza.
Fatto questo abbiamo creato il collegamento Drill-through che volevamo.