Ripartizione quote

Non è propriamente un programma di BI, ma nelle sue versioni più recenti anche Excel può fare grandi cose!

Moderatore: Utilizzo_prof_Excel

Rispondi

DarioDalfe
Messaggi: 11 | Topic creati
Iscritto il: mer 22 lug 2020, 19:31
Ringraziato: 1 volta

Ripartizione quote

Messaggio da DarioDalfe »

Buongiorno a tutti,

ho elaborato un modello per sintetizzare i costi previsti suddiviso per macro tipologie. Su ogni riga è stata indicata una commessa di riferimento sulla quale imputare i costi.
Ogni commessa però può essere a sua volta ripartita tra diversi settori in quote %.
Non riesco però a realizzare una tabella, con le relative misure, per esporre i valori già ripartiti per settore.
Vi allego un esempio.
Grazie a chi vorrà aiutarmi

Dario
Allegati
Bdg.xlsx
(785.03 KiB) Scaricato 14 volte


Avatar utente

Enrico Galli
Messaggi: 890 | Topic creati
Iscritto il: dom 28 giu 2020, 19:03
Luogo: San Giovanni in Persiceto (BO)
Ringraziato: 325 volte
Contatta:

Ripartizione quote

Messaggio da Enrico Galli »

Ciao, è questo l'output che ti aspetti?

Immagine

Se sì, cambia le tue misure in questo modo:

Codice: Seleziona tutto

//Codice DAX esportato con Excel Swiss Knife - www.excelswissknife.com
//Formattato da DAX Formatter - www.daxformatter.com

//QuotaRip
=
SUMX (
    Tb_RipSettori;
    Tb_RipSettori[Quota]
)
 
//Costo CF
=
CALCULATE (
    SUM ( 'Exp_Bdg'[Imp] );
    CROSSFILTER ( 'Tb_AnaCom'[Id_Commessa]; 'Tb_RipSettori'[Id_Commessa]; BOTH )
)
 
//ImpRip
=
[Costo CF] * [QuotaRip]
 
Enrico Galli
Link utili: I nostri tutorial | Come inserire: Immagini - Codice - Risolto
Se il forum ti è stato utile, considera di supportarlo con una libera donazione

Autore del topic
DarioDalfe
Messaggi: 11 | Topic creati
Iscritto il: mer 22 lug 2020, 19:31
Ringraziato: 1 volta

Ripartizione quote

Messaggio da DarioDalfe »

Grazie mille Enrico,

la misura corretta sembra quella "Costo CF". Infatti il totale complessivo coincide con quello che risulta dal totale della colonna "imp" (1.020.964)
Come ragiona questa misura?

Immagine

Se apro però il dettaglio della ripartizione per settori (esempio struttura\Utenze & Ufficio) la somma esposta dei tre settori (41.076) non coincide con quello riportato senza drill down ( 35.940)


Grazie ancora
Avatar utente

Enrico Galli
Messaggi: 890 | Topic creati
Iscritto il: dom 28 giu 2020, 19:03
Luogo: San Giovanni in Persiceto (BO)
Ringraziato: 325 volte
Contatta:

Ripartizione quote

Messaggio da Enrico Galli »

Non sono convinto di questo modello... tu metti "Settore" come dipendente gerarchico di "Categoria" e "Sottocategoria", ma queste ultime sono campi della fact table, mentre "Settore" è una dimensione (la più "lontana" del tuo snowflake). Faccio fatica a capire la logica... secondo me c'è da rivedere la disposizione dei dati. Io ho cercato di "rimediare" a uno dei problemi che c'erano mettendo un CROSSFILTER che permette di passare il filtro dalla tabella Tb_RipSettori alla tabella Tb_AnaCom, ma sicuramente non ho risolto tutto. Purtroppo adesso non ho molto tempo da dedicare allo studio del caso, ma magari Andrea90 o qualcun altro riusciranno al volo a fare una valutazione e darti qualche consiglio
Enrico Galli
Link utili: I nostri tutorial | Come inserire: Immagini - Codice - Risolto
Se il forum ti è stato utile, considera di supportarlo con una libera donazione
Avatar utente

Andrea90
Messaggi: 2193 | Topic creati
Iscritto il: dom 28 giu 2020, 19:41
Luogo: Bologna
Ringraziato: 666 volte
Contatta:

Ripartizione quote

Messaggio da Andrea90 »

Ciao DarioDalfe,

Non posso che concordare con Enrico. La "bontà" di una Fact Table la si vede dal livello informativo che ciascuna riga può fornire. Le tue sono per la maggior parte righe identiche, con tantissime ripetizioni della stessa informazione (il che è dovuto dal fatto che hai unpivotizzato le tabelle per avere una distribuzione dell'importo nei vari periodi.

Ma questo non serve farlo in PowerQuery, puoi ottenere lo stesso risultato semplicemente lasciando una sola riga, con il valore di importo e di inizio/fine periodo di validità. A quel punto puoi eventualmente calcolarti già un importo giornaliero, e a quel punto, tramite una tabella Calendar puoi ottenere facilmente le ripartizioni per mese/trimestre/anno, andando a filtrare la tabella Calendar per i giorni compresi (nell'attuale contesto filtro) tra le date di inizio e fine.

In questo modo hai già eliminato gran parte delle ripetizioni. Puoi anche prevedere di mantenere nella fact table solo la chiave univoca del progetto/commessa (questo ce lo devi dire tu quale può essere il campo univoco), l'importo, data inizio, data fine, importo giornaliero.

Tutte le altre informazioni anagrafiche le puoi salvare in una lookup table.

Per quanto riguarda i settori, la strada a mio avviso più semplice è quella di "duplicare" le righe della fact table per ciascuna combinazione "commessa - settore" disponibile (per quella specifica commessa ovviamente). Così da avere fin da subito l'importo suddiviso per settore. Duplicherai delle righe è vero, ma queste righe avranno un valore informativo univoco, poiché saranno legate ad una specifica combinazione "commessa-settore" (cosa non così facile da ottenere, come invece lo è quella della ripartizione del costo nei vari archi temporali di interesse).

La modellizzazione del dato è molto importante, e prima di partire con i report è necessario prendersi il giusto tempo per studiare quale sia la migliore possibile per il tipo di analisi che si intende fare. Anche quello che ti ho detto adesso è un'indicazione sommaria, ottenuta dando appena un'occhiata al tuo modello attuale. Andrebbe studiata meglio la cosa per capirne l'effettiva validità.

A presto,
Andrea
Se hai gradito l'aiuto che hai ricevuto considera di contribuire alle spese per il mantenimento del forum facendo una libera DONAZIONE --> Link

Ricordarsi di segnare come "RISOLTE" le discussioni per le quali si è ricevuto un feedback positivo. Per vedere come fare --> Link

Autore del topic
DarioDalfe
Messaggi: 11 | Topic creati
Iscritto il: mer 22 lug 2020, 19:31
Ringraziato: 1 volta

Ripartizione quote

Messaggio da DarioDalfe »

Grazie Andrea, se hai qualche esempio di come potrei eliminare la unpivot sono ben disposto a cambiare, purtroppo non so destreggiarmi benissimo con le tabelle calendar e il dax.
Se funziona posso eliminare anche le ripartizioni mensili gia all'interno delle tabelle di input
Sono aperto a qualsiasi suggerimento /istruzione

D.
Avatar utente

Andrea90
Messaggi: 2193 | Topic creati
Iscritto il: dom 28 giu 2020, 19:41
Luogo: Bologna
Ringraziato: 666 volte
Contatta:

Ripartizione quote

Messaggio da Andrea90 »

Ciao DarioDalfe,

Ti giro un esempio riciclando per la terza volta un file demo (ormai ho notato che le domande relative ai calcoli "temporali" sono parecchio gettonate dagli utenti, magari mi metterò a scrivere qualche tutorial a riguardo :lol: ).

Comunque il foglio che interessa a te è quello chiamato "Ripartizione". Vedrai che ho costruito una tabellina con 3 commesse, data inizio, fine, e importo. Dentro a PowerQuery ho calcolato l'importo giornaliero.

Poi col DAX ho creato questa misura:

Codice: Seleziona tutto

=
VAR CurStartDate =
    CALCULATE ( MAX ( Commesse[Data Inizio] ); ALL ( 'Calendar' ) )
VAR CurEndDate =
    CALCULATE ( MAX ( Commesse[Data Fine] ); ALL ( 'Calendar' ) )
VAR ImportoGG =
    CALCULATE ( MAX ( Commesse[Importo Giorn.] ); ALL ( 'Calendar' ) )
VAR TempTable =
    FILTER (
        VALUES ( 'Calendar'[Date] );
        'Calendar'[Date] >= CurStartDate
            && 'Calendar'[Date] <= CurEndDate
    )
VAR Result =
    COUNTROWS ( TempTable ) * ImportoGG
RETURN
    Result
La quale ti esegue la ripartizione, in questo caso per anno-mese, ma puoi calcolarla in modo diverso semplicemente cambiando le dimensioni (trimestre, settimane, anni, ecc ecc).

A presto,
Andrea
Allegati
TestData.xlsx
(578.82 KiB) Scaricato 16 volte
Se hai gradito l'aiuto che hai ricevuto considera di contribuire alle spese per il mantenimento del forum facendo una libera DONAZIONE --> Link

Ricordarsi di segnare come "RISOLTE" le discussioni per le quali si è ricevuto un feedback positivo. Per vedere come fare --> Link

Autore del topic
DarioDalfe
Messaggi: 11 | Topic creati
Iscritto il: mer 22 lug 2020, 19:31
Ringraziato: 1 volta

Ripartizione quote

Messaggio da DarioDalfe »

grazie Andrea,

ho provato subito ad utilizzare la tabella calendar e dax per le ripartizioni mensili.
Purtroppo gli errori di somma sulla tabella pivot non sono risolti (i dati espansi non sono la somma dei dati compressi). sotto un esempio.
Immagine

Immagine

Per le ripartizioni per commessa ho pensato di effettuare un merge e moltiplicare poi l'importo di riga per la quota. Fatemi sapere cose ne pensate.
Avatar utente

Andrea90
Messaggi: 2193 | Topic creati
Iscritto il: dom 28 giu 2020, 19:41
Luogo: Bologna
Ringraziato: 666 volte
Contatta:

Ripartizione quote

Messaggio da Andrea90 »

Ciao DarioDalfe,

Il mio era solo un file demo, completamente separato da quello su cui stai lavorando tu, che doveva servire solo a mostrarti come è possibile riuscire ad ottenere una ripartizione dei valori senza passare dal modello dati (duplicazione di righe), ma utilizzando il Dax.

Poi lo scopo del forum è quello di instradare gli utenti (per quanto possibile) a risolvere i loro problemi, ma ciò non significa che bisogna prendere le soluzioni date e copiarle/incollarle nel proprio file senza prima studiarne le logiche. Come ti avevo scritto in un messaggio precedente sarà necessario creare già nella fact table la suddivisione per i diversi settori di una commessa, ed in più dovrai poi vedere come adattare il codice che ti ho fornito per calcolare i diversi subtotali.

A presto,
Andrea
Se hai gradito l'aiuto che hai ricevuto considera di contribuire alle spese per il mantenimento del forum facendo una libera DONAZIONE --> Link

Ricordarsi di segnare come "RISOLTE" le discussioni per le quali si è ricevuto un feedback positivo. Per vedere come fare --> Link
Rispondi