Istruzione condizionale su righe differenti

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

Moderatore: Utilizzo_prof_Excel

Rispondi
Avatar utente

Davide.par
Messaggi: 21 | Topic creati
Iscritto il: ven 25 dic 2020, 8:38
Ringraziato: 2 volte
Contatta:

Istruzione condizionale su righe differenti

Messaggio da Davide.par »

Buongiorno a tutti e complimenti per il Forum.

Da qualche mese mi sto avvicinando a Power Query e Power Pivot e non sono ancora riuscito a replicare quello che su excel farei con una =SE() che lavora su celle che stanno su righe differenti.
Nell’esempio che allego, dato il venduto di varie coppie prodotto/negozio vorrei identificare i record che – cumulati – raggiungono l‘80% del totale per prodotto.
In excel sono riuscito a farlo nella colonna „Raggiunto 80%?“ con una =SE(), dopo aver preventivamente calcolato il Venduto % e il Venduto % cumulato di ogni coppia prodotto/negozio.

=SE(O (A2<>A1;F2<0,8;E(F2>0,8;F1<0,8));"x";"")

In Power Query riesco a calcolare entrambe le percentuali, ma sono fermo quando si tratta di selezionare i record che soddisfano quella particolare condizione.
Stessa cosa in Power Pivot. Tutto liscio fino alle percentuali, ma non saprei come replicare la =SE() con una misura.

Riuscite a darmi qualche dritta per farlo sia in PQ che in PP?
Grazie,
Davide
Allegati
Venduto% cumulato.xlsx
(161.01 KiB) Scaricato 21 volte


Avatar utente

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

Istruzione condizionale su righe differenti

Messaggio da Andrea90 »

Ciao Davide.par,

Per far quello che chiedi in PowerPivot serve una colonna calcolata e non una misura, poiché stai in qualche modo categorizzando una data dimensione, ad esempio il prodotto (ordinato in senso decrescente per il totale venduto).

A questo punto preferisco fare il tutto in PowerQuery piuttosto che con il DAX (non tanto perché non sia fattibile, anzi, ma più per utilizzare PQ fintanto che si tratta di preparare il modello dati).

Detto questo la cosa che mi lascia perplesso è la formula utilizzata, ed in particolare la riga 23. Per le analisi 80/20, o per la categorizzazione ABC, di solito si fissa un limite, ad esempio 80% calcolato sulla percentuale cumulata. Se il record ha un valore inferiore all'80% allora riceve un dato valore di categoria, altrimenti un altro.

Quello che fai tu è invece differenziare tra il primo valore che riceve un dato cumulato pari o superiore ad 80% e tutti i rimanenti. Mi spiego, alla riga 23 il prodotto ha un valore pari a 81% e rientra in una categoria (x) mentre la riga successiva con un valore pari a 89% non ci rientra.

Per farti un esempio, in DAX, una volta calcolata la cumulata % si utilizza una formula simile:

Codice: Seleziona tutto

SWITCH (
    TRUE,
    'nomeTab'[%Cum] <= 0.8, "x",
    BLANK()
)
Così da assegnare "x" a tutti i record con valore inferiore o uguale a 0.8 e vuoto a tutti gli altri.

Ma con la logica che utilizzi tu questo non sarebbe possibile poiché nella riga 23 tu vedresti vuoto invece che "x".

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
Avatar utente

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

Istruzione condizionale su righe differenti

Messaggio da Andrea90 »

ps. quella formula con SWITCH scritta in quel modo può essere sostituita con un semplice IF. In DAX lo SWITCH può essere utile quando devi costruire delle condizioni IF annidate (un po’ come il Select Case in VBA).

In Power Query puoi costruire il tutto con degli if, ma prima di proporre una soluzione vorrei provare a capire meglio che logica intendi utilizzare.

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
Avatar utente

Autore del topic
Davide.par
Messaggi: 21 | Topic creati
Iscritto il: ven 25 dic 2020, 8:38
Ringraziato: 2 volte
Contatta:

Istruzione condizionale su righe differenti

Messaggio da Davide.par »

Ciao Andrea90 e grazie per la risposta,

Si‘, in effetti ho pensato a qualcosa di diverso da una classica ABC.
Questo perche‘ ho spesso a che fare con prodotti che sono venduti in un solo negozio e dove quindi l’unico record per quel prodotto ha un cumulato% pari a 100% (oppure dove con il primo negozio arrivo al 90% e con il secondo arrivo al 100%). E adesso che ci penso avrei potuto inserirne uno nel file di esempio :-) .
Anche in questi casi dovrei poter identificare i negozi che mi coprono l‘80% del totale, indipendentemente dal fatto che si tratti di uno o di n negozi, andando poi a fare un’azione commerciale solo su questi e non su quelli piu‘ piccoli.

Nel caso di un prodotto venduto in un solo negozio (con cumulata pari a 100%), questa formula restituirebbe vuoto e quindi non farebbe al caso mio:

=SE(%Cum <= 0,8; "x";"")

Per questa ragione, pur partendo da una formula di questo tipo l'ho integrata e ho seguito quel tipo di logica.
A presto
Davide
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:

Istruzione condizionale su righe differenti

Messaggio da Enrico Galli »

Ciao! Io invece voglio fare "l'avvocato del diavolo" e ti propongo una soluzione in DAX :mrgreen:
Per fare la cumulata ho semplicemente sommato tutte le vendite maggiori o uguali a quella della riga corrente (simulandone così l'ordinamento decrescente), poi per le "x" ho implementato una SWITCH(TRUE()). Prova a guardare il codice e il file allegato e dimmi se ti ritrovi :wave:
(P.S: nota che, lavorando con le misure DAX, l'unica colonna necessaria nella base dati è il totale venduto)

Codice: Seleziona tutto

//Totale venduto
=
SUM ( DatiPP[Venduto] )
 

//Totale prodotto
=
CALCULATE ( [Totale venduto]; ALL ( DatiPP[Negozio] ) )
 

//% venduto
=
DIVIDE ( [Totale venduto]; [Totale prodotto] )
 

//% cumulata
=
VAR venduto = [Totale venduto]
VAR migliori =
    CALCULATE (
        [Totale venduto];
        ALL ( DatiPP[Negozio] );
        DatiPP[Venduto] >= venduto
    )
VAR result =
    DIVIDE ( migliori; [Totale prodotto] )
RETURN
    result
 

//% cumulata precedente
=
VAR venduto = [Totale venduto]
VAR migliori =
    CALCULATE (
        [Totale venduto];
        ALL ( DatiPP[Negozio] );
        DatiPP[Venduto] > venduto
    )
VAR result =
    DIVIDE ( migliori; [Totale prodotto] )
RETURN
    result
 

//Supera 80%
=
SWITCH (
    TRUE ();
    [% cumulata] < 0,8; "x";
    AND ( [% cumulata] >= 0,8; [% cumulata precedente] < 0,8 ); "x";
    ""
)
 

Allegati
Venduto% cumulato.xlsx
(125.79 KiB) Scaricato 18 volte
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:

Istruzione condizionale su righe differenti

Messaggio da Andrea90 »

Ciao,

Mmm mi verrebbe da dire di aggiungere una condizione che verifichi se il valore della vendita per la riga che si sta analizzando è uguale al MAX delle vendite per quel dato prodotto. In questo modo se c’è solo una riga per un prodotto allora riceverà comunque l assegnazione, mentre se c’è ne fossero 2 (con la prima riga già superiore a 80%) allora solo la prima riga riceverebbe l assegnazione della categoria.

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
Avatar utente

Autore del topic
Davide.par
Messaggi: 21 | Topic creati
Iscritto il: ven 25 dic 2020, 8:38
Ringraziato: 2 volte
Contatta:

Istruzione condizionale su righe differenti

Messaggio da Davide.par »

Ciao a tutti e due e grazie!

si‘ si‘, Enrico la soluzione in DAX funziona ed e‘ un ottimo punto di partenza per andarmi ad approndire le formule che hai impiegato. Il procedimento mi torna e buona parte della sintassi anche.

Andrea, si‘, ero sicuro che la formula si potesse migliorare. L’ho riguardata e credo che un controllo sulla cella che sta sulla riga precedente sia comunque necessario per garantire il superamento dell‘80% (P.S e questa sarebbe proprio la parte che mi piacerebbe riuscire a fare in PQ).
Qundi se ho ben capito mi ritrovo qui

=SE(O([@Venduto]=MAX.SE([Venduto];[Prodotto];[@Prodotto]);F2<0,8;E(F2>0,8;F1<0,8));"x";"")

Pensate si possa impostare anche in PQ una condizione di questo tipo?

Ciao e grazie ancora,
Davide
Avatar utente

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

Istruzione condizionale su righe differenti

Messaggio da Andrea90 »

Ciao Davide.par,

Io vedo che inserendo la tua formula dentro alla tabella mi compare lo stesso risultato della formula originale.
Rimango dunque con il dubbio di come mai tu voglia considerare la riga 23 appartenente ad una categoria differente alla riga 24.

Pertanto posso solo fornirti come input un esempio su come poter far riferimento ad un dato della riga precedente in PQ (puoi creare una query vuota dentro al file che hai allegato ed incollare al suo interno lo script che segue):

Codice: Seleziona tutto

let
    Origine = Excel.CurrentWorkbook(){[Name="Dati"]}[Content],
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Prodotto", type text}, {"Negozio", type text}, {"Venduto", Int64.Type}, {"Totale Prodotto", Int64.Type}, {"Venduto %", type number}, {"Venduto % cumulato", type number}, {"Raggiunto 80%?", type text}}),
    #"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo",{"Totale Prodotto", "Venduto %", "Venduto % cumulato", "Raggiunto 80%?"}),
    #"Aggiunta colonna indice" = Table.AddIndexColumn(#"Rimosse colonne", "Indice", 0, 1, Int64.Type),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Aggiunta colonna indice", "ValorePrecedente", each try List.Buffer(#"Rimosse colonne"[Venduto]){[Indice]-1} otherwise "", Int64.Type)
in
    #"Aggiunta colonna personalizzata"
Detto questo, sconsiglio questo approccio, poiché chiedere a PQ di analizzare riga per riga è un'operazione dispendiosa in termini di risorse.

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
Avatar utente

Autore del topic
Davide.par
Messaggi: 21 | Topic creati
Iscritto il: ven 25 dic 2020, 8:38
Ringraziato: 2 volte
Contatta:

Istruzione condizionale su righe differenti

Messaggio da Davide.par »

Ciao Andrea,

fatto! Con la List.Buffer e un paio di IF sono riuscito a riprodurre il tutto anche in PQ. Grazie!
Chiaro anche per quanto riguarda il tuo consiglio di evitare questa soluzione in PQ per ragioni di efficienza.

Per quanto riguarda le righe 23 e 24 a me sembra tutto a posto.
La riga 23 e‘ la prima che con il cumulato raggiunge l‘80%. Per quel dato prodotto mi occorre cioe' marcare tutte le righe fino alla 23 compresa.
Tutte le righe successive (compresa la 24) non sono da marcare proprio perche‘ la soglia dell‘80% e‘ stata gia‘ raggiunta.

Immagine

Grazie ancora per l’aiuto e buon proseguimento
Davide
Rispondi