Rank a più livelli standard L’argomento è risolto

[Marco Lubry]

Salve a tutti. Vi seguo da molto tempo. Prima volta che scrivo.
Vado subito alla problematica. Ho bisogno di creare un Rank a partita di "Periodo", "Codice obiettivo", sul campo "Delta cons/Obj periodo %".
Per questo motivo, sulla tabella Consuntivo,
- eseguo l'ordinamento per Periodo e Codice Obiettivo
- faccio il raggruppamento
- aggiungo una colonna indice
- espando la tabella

tutto funziona perfettamente, tranne per l'obiettivo 080 dove si verificano delle eguaglianze a parità di "Delta cons/Obj periodo %". Non so con quale criterio ma il Rank fa comunque una classifica randomica per ogni periodo da 1 a 17, mentre io vorrei avere 15 filiali con il Rank = 1, e l'ultima con il rank = 16 (rank standard, non denso).
Ho necessità di calcolare il Rank in power query e non il power BI, per potermi calcolare altre colonne

Allego il pbxi. Spero sia sufficiente.
ringrazio in anticipo per l'aiuto
Marco

[Andrea90]

@Marco Lubry

Questo è lo script di Power Query della tua tabella consuntivi (premetto che non possiamo analizzare il risultato finale perché le tue origini dati sono dei caricamenti da cartella e dunque, non avendone l'accesso non possiamo eseguire i passaggi):

Codice: Seleziona tutto

let
    Origine = Folder.Files("C:\Users\mlubrano\OneDrive - ICCREA\General\Progetti\BCC Pergola e Corinaldo - Dashboard Raccolta ed Impieghi\Dati\Consuntivi x Mese"),
    #"File nascosti filtrati1" = Table.SelectRows(Origine, each [Attributes]?[Hidden]? <> true),
    #"Richiama funzione personalizzata1" = Table.AddColumn(#"File nascosti filtrati1", "Trasforma file (2)", each #"Trasforma file (2)"([Content])),
    #"Rinominate colonne1" = Table.RenameColumns(#"Richiama funzione personalizzata1", {"Name", "Source.Name"}),
    #"Rimosse altre colonne1" = Table.SelectColumns(#"Rinominate colonne1", {"Source.Name", "Trasforma file (2)"}),
    #"Espansa colonna table1" = Table.ExpandTableColumn(#"Rimosse altre colonne1", "Trasforma file (2)", Table.ColumnNames(#"Trasforma file (2)"(#"File di esempio (2)"))),
    #"Rimosse altre colonne" = Table.SelectColumns(#"Espansa colonna table1",{"Source.Name", "Periodo", "Cod_Fil", "Cod Obj", "Consuntivo Y","Peso Num","Peso Den"}),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Rimosse altre colonne",{{"Source.Name", type text}, {"Periodo", type date}, {"Cod_Fil", Int64.Type}, {"Cod Obj", type text}, {"Consuntivo Y", type number}, {"Peso Num", type number}, {"Peso Den", type number}}),
    #"Merge Obiettivi x Anno" = Table.NestedJoin(#"Modificato tipo", {"Periodo", "Cod_Fil", "Cod Obj"}, #"Obiettivi x Anno", {"Periodo", "Cod_Fil", "Cod Obj"}, "Obiettivi x Anno", JoinKind.LeftOuter),
    #"Tabella Obiettivi x Anno espansa" = Table.ExpandTableColumn(#"Merge Obiettivi x Anno", "Obiettivi x Anno", {"Obiettivo Finale", "Obiettivo Intermedio", "Anno obj", "Mese obj", "Anno Calendario", "Obiettivo Periodo"}, {"Obiettivo Finale", "Obiettivo Intermedio", "Anno obj", "Mese obj", "Anno Calendario", "Obiettivo Periodo"}),
    #"Merge di query eseguito" = Table.NestedJoin(#"Tabella Obiettivi x Anno espansa", {"Cod Obj"}, #"Anagrafica Obiettivi", {"Cod Obj"}, "Anagrafica Obiettivi", JoinKind.LeftOuter),
    #"Tabella Anagrafica Obiettivi espansa" = Table.ExpandTableColumn(#"Merge di query eseguito", "Anagrafica Obiettivi", {"Obj Descrizione", "Segno"}, {"Obj Descrizione", "Segno"}),
    #"Aggiunta Inverti Segno" = Table.AddColumn(#"Tabella Anagrafica Obiettivi espansa", "Delta Cons/Obj Periodo", each if [Segno] = "Inv" then [Obiettivo Periodo] - [Consuntivo Y] else [Consuntivo Y]-[Obiettivo Periodo]),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta Inverti Segno",{{"Delta Cons/Obj Periodo", type number}}),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Modificato tipo1", "Delta Cons/Obj Periodo %", each if ([Obiettivo Periodo] > 0 and [Obiettivo Periodo] <> null) then [#"Delta Cons/Obj Periodo"] / [Obiettivo Periodo] else 0),
    #"Modificato tipo2" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata1",{{"Delta Cons/Obj Periodo %", Percentage.Type}}),
    #"Merge di query eseguito1" = Table.NestedJoin(#"Modificato tipo2", {"Cod_Fil"}, #"Anagrafica Filiali", {"Cod_Fil"}, "Anagrafica Filiali", JoinKind.LeftOuter),
    #"Tabella Anagrafica Filiali espansa1" = Table.ExpandTableColumn(#"Merge di query eseguito1", "Anagrafica Filiali", {"Codice HUB", "DESC HUB", "Filiale"}, {"Codice HUB", "DESC HUB", "Filiale"}),
    #"Merge di query eseguito2" = Table.NestedJoin(#"Tabella Anagrafica Filiali espansa1", {"Anno obj", "Mese obj"}, Calendario, {"Anno obj", "Mese obj"}, "Calendario", JoinKind.LeftOuter),
    #"Tabella Calendario espansa" = Table.ExpandTableColumn(#"Merge di query eseguito2", "Calendario", {"Tipo OBJ"}, {"Tipo OBJ"}),
    #"Aggiunta colonna personalizzata7" = Table.AddColumn(#"Tabella Calendario espansa", "Anno obj Y -1", each [Anno obj] - 1),
    #"Modificato tipo8" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata7",{{"Anno obj Y -1", Int64.Type}}),
    #"Merge di query eseguito3" = Table.NestedJoin(#"Modificato tipo8", {"Anno obj Y -1", "Cod_Fil", "Cod Obj"}, #"Consuntivo Y-1", {"Anno obj", "Cod_Fil", "Cod Obj"}, "Consuntivo Y-1", JoinKind.LeftOuter),
    #"Tabella Consuntivo Y-1 espansa" = Table.ExpandTableColumn(#"Merge di query eseguito3", "Consuntivo Y-1", {"Consuntivo Y"}, {"Consuntivo Y-1.Consuntivo Y"}),
    #"Rinominate colonne" = Table.RenameColumns(#"Tabella Consuntivo Y-1 espansa",{{"Consuntivo Y-1.Consuntivo Y", "Consuntivo Y-1"}, {"Obj Descrizione", "Obiettivi Descrizione"}}),
    #"Ordinate righe" = Table.Sort(#"Rinominate colonne",{{"Periodo", Order.Ascending}, {"Cod Obj", Order.Ascending}, {"Delta Cons/Obj Periodo %", Order.Descending}}),
    #"Raggruppate righe1" = Table.Group(#"Ordinate righe", {"Periodo", "Cod Obj"}, {{"Conteggio", each _, type table [Source.Name=nullable text, Periodo=nullable date, Cod_Fil=nullable number, Filiale=nullable text, Cod Obj=nullable text, Obiettivi Descrizione=nullable text, Consuntivo Y=nullable number, Peso Num=nullable number,Peso Den=nullable number, Obiettivo Periodo=nullable number, Segno=nullable text, Anno obj=nullable number, Mese obj=nullable number, Anno Calendario=nullable number, Obiettivo Finale=nullable number, Obiettivo Intermedio=nullable number,#"Delta Cons/Obj Periodo"=nullable number, #"Delta Cons/Obj Periodo %"=nullable number, Codice HUB=nullable text, DESC HUB=nullable text, Tipo OBJ=nullable text, #"Anno obj Y-1"=nullable number, #"Consuntivo Y-1"=nullable number]}}),
    #"Aggiunta colonna indice" = Table.AddColumn(#"Raggruppate righe1", "Indice", each Table.AddIndexColumn([Conteggio],"Rank",1,1)),
    #"Rimosse altre colonne2" = Table.SelectColumns(#"Aggiunta colonna indice",{"Indice"}),
    #"Tabella Indice espansa1" = Table.ExpandTableColumn(#"Rimosse altre colonne2", "Indice", {"Source.Name", "Periodo", "Cod_Fil", "Filiale", "Cod Obj", "Obiettivi Descrizione", "Consuntivo Y", "Peso Num", "Peso Den", "Anno Calendario", "Anno obj", "Mese obj", "Obiettivo Periodo", "Segno", "Obiettivo Finale", "Obiettivo Intermedio", "Delta Cons/Obj Periodo", "Delta Cons/Obj Periodo %", "Rank", "Codice HUB", "DESC HUB","Tipo OBJ", "Anno obj Y-1", "Consuntivo Y-1"}, {"Source.Name", "Periodo", "Cod_Fil", "Filiale", "Cod Obj", "Obiettivi Descrizione", "Consuntivo Y", "Peso Num", "Peso Den", "Anno Calendario", "Anno obj", "Mese obj", "Obiettivo Periodo", "Segno", "Obiettivo Finale", "Obiettivo Intermedio", "Delta Cons/Obj Periodo", "Delta Cons/Obj Periodo %", "Rank", "Codice HUB", "DESC HUB","Tipo OBJ","Anno obj Y-1", "Consuntivo Y-1"}),
    #"Modificato tipo3" = Table.TransformColumnTypes(#"Tabella Indice espansa1",{{"Periodo", type date}, {"Cod_Fil", Int64.Type}, {"Filiale", type text}, {"Cod Obj", type text}, {"Consuntivo Y", type number},{"Peso Num", type number},{"Peso Den", type number}, {"Anno Calendario", Int64.Type}, {"Mese obj", Int64.Type}, {"Obiettivo Finale", type number}, {"Obiettivo Intermedio", type number}, {"Delta Cons/Obj Periodo", type number}, {"Delta Cons/Obj Periodo %", Percentage.Type}, {"Rank", Int64.Type}, {"Obiettivi Descrizione", type text}, {"Obiettivo Periodo", type number}, {"Anno obj", Int64.Type}, {"DESC HUB", type text}, {"Codice HUB", type text}, {"Tipo OBJ", type text}}),
    #"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Modificato tipo3", "Delta Cons/Obj Intermendio", each if [Segno]  = "Inv" 
then [Obiettivo Intermedio] - [Consuntivo Y] 
else [Consuntivo Y] - [Obiettivo Intermedio]),
    #"Modificato tipo4" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata2",{{"Delta Cons/Obj Intermendio", type number}}),
    #"Sostituito valore" = Table.ReplaceValue(#"Modificato tipo4",null,0,Replacer.ReplaceValue,{"Delta Cons/Obj Intermendio"}),
    #"Sostituito valore1" = Table.ReplaceValue(#"Sostituito valore",null,0,Replacer.ReplaceValue,{"Obiettivo Intermedio"}),
    #"Aggiunta colonna personalizzata3" = Table.AddColumn(#"Sostituito valore1", "Delta Cons/Obj Intermendio %", each if ([Obiettivo Intermedio] <> 0 and [Obiettivo Intermedio] <> null) then [#"Delta Cons/Obj Intermendio"] / [Obiettivo Intermedio]
else 0),
    #"Modificato tipo6" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata3",{{"Delta Cons/Obj Intermendio %", Percentage.Type}}),
    #"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo6",{"Source.Name"}),
    #"Aggiunta colonna personalizzata5" = Table.AddColumn(#"Rimosse colonne", "Risultato Intermedio", each if ([#"Delta Cons/Obj Periodo"] > 0 and [#"Delta Cons/Obj Periodo"] <> null and [Mese obj] = 10) then "1" else "0"),
    #"Aggiunta colonna personalizzata4" = Table.AddColumn(#"Aggiunta colonna personalizzata5", "Risultato Finale ", each if ([#"Delta Cons/Obj Periodo"] > 0 and [#"Delta Cons/Obj Periodo"] <> null and [Mese obj] = 12) then "1" else "0"),
    #"Modificato tipo7" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata4",{{"Risultato Intermedio", Int64.Type}, {"Risultato Finale ", Int64.Type}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo7", "Delta Cons/Obj Finale", each if [Segno]  = "Inv" 
then [Obiettivo Finale] - [Consuntivo Y] 
else [Consuntivo Y] - [Obiettivo Finale]),
    #"Modificato tipo5" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"Delta Cons/Obj Finale", type number}}),
    #"Aggiunta colonna personalizzata6" = Table.AddColumn(#"Modificato tipo5", "Delta Cons/Obj Finale %", each if [Obiettivo Finale] > 0 and [Obiettivo Finale] <> null  then [#"Delta Cons/Obj Finale"]/[Obiettivo Finale]
else 0),
    #"Modificato tipo9" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata6",{{"Delta Cons/Obj Finale %", Percentage.Type}})
in
    #"Modificato tipo9"

Ti suggerisco vivamente di cominciare a pulire il codice perché è quasi sempre "inutile" ripetere n-volte il passaggio Modificato tipo, basta farlo una sola volta alla fine. Idem le colonne che aggiungi tramite Table.AddColumn(), dove lasci il nome generico che fornisce il sistema. Come fai, semplicemente leggendo questo tracciato (cosa che dovremmo fare noi visto che non sappiamo le logiche da te applicate) a capire in modo chiaro quello che stai facendo?

Quando si allega un file demo, relativo ad uno specifico problema è meglio allegare un qualcosa di limitato (con una sorgente dati disponibile anche a noi) a poche righe e che mostri il risultato desiderato partendo da un dato input.
Altrimenti è come chiedere di mettere mano dal principio, all'intero progetto, e questo è più vicino ad una consulenza piuttosto che un aiuto gratuito su di un forum.

Andrea

[Marco Lubry]

Hai ragione sorry.
ma per velocizzare l'invio avevo lasciato tutto come come proposto da PBI.
Ad ogni modo ripropongo un nuovo pbxi ed aggiungo anche la base dati che dovrebbe essere unzippata su c:\Prova Ranx\.

Ricordo che il problema è l'obiettivo '080' dove vorrei ottenere un Rank standard non denso (stesso numero di rank a parità do oggetto utilizzato per il calcolo del rank)

Saluti
Marco

[Andrea90]

@Marco Lubry

Dopo aver eseguito il passaggio di "Raggruppate Righe" non serve aggiungere una colonna indice per calcolare il rank. Esiste una formula che fa questo ed è Table.AddRankColumn().

Quindi crea una colonna calcolata ed aggiungi questo codice:

Codice: Seleziona tutto

Table.AddRankColumn([Conteggio], "Rank", {"Delta Cons/Obj Periodo"})

Andrea

[Marco Lubry]

Grazie Andrea....in realtà lo avevo già provato. Funziona...ma esattamente al contrario....
Io prima del raggruppa righe faccio l'ordinamento per "Periodo" Asc, "Cod Obj" Asc, "Delta Cons/Obj periodo %" desc e Cod_Fil Asc.
Quindi mi attendo il Rank esattamente in questo ordine.
Ma il risultato è proprio l'inverso.
Chat GPT mi aveva suggerito di inserire l'opzione order come di seguito riportato....
= Table.AddColumn(#"Raggruppate righe", "Indice", each Table.AddRankColumn([Conteggio], "Rank", {"Delta Cons/Obj Periodo %"}, Order.Descending))

ma in realtà non funziona...non accetta l'order.descending
per questo avevo deciso di puntare sulla colonna indice che almeno mi dava l'ordinamento corretto.

Allego comunque Pbxi con la colonna Rank inserita come da tuo suggerimento.

[Andrea90]

@Marco Lubry

Se funziona ed il problema è solo l’ordinamento le soluzioni si trovano… ad esempio, potresti calcolarti il rank sul valore del campo, ma con il segno cambiato.
Oppure se vuoi invertire il valore del rank (il valore più piccolo diventa il più grande e viceversa), puoi fare:
[Valore Massimo] - [Valore del rank] + 1

Andrea

[Marco Lubry]

Allora
non so se ho fatto esattamente quello che dicevi tu....ma ora funziona.
Mi sono creato una nuova colonna sul campo del rank * (-1).
A questo punto ho costruito la colonna Rank su questo campo...ed ha funzionato.

Non sono riuscito a cambiare il segno direttamente nell'istruzione
= Table.AddColumn(#"Raggruppate righe1", "Indice", each Table.AddRankColumn([Conteggio], "Rank", {"Delta Cons/Obj periodo %"}))
magari sbaglio qualcosa io....

Ad ogni modo, ora è ok con un passaggio in più

Ti ringrazio molto, ed ancora complimenti per questo ottimo forum

m

[Andrea90]

Chat GPT....

Eh si, va di moda utilizzarlo... peccato che per certi lavori non è in grado di dare un supporto concreto, anzi alle volte, pur di darti ragione, arriva a suggerire cose sbagliate, specie se si lavora con i modelli dati e le misure in DAX.

Questa è la sintassi da usare per impostare l'ordinamento:

Codice: Seleziona tutto

Table.AddRankColumn([Conteggio], "Rank", {"Delta Cons/Obj Periodo %", Order.Descending})

E non serve Chat GPT, basta leggere la documentazione ufficiale:

https://learn.microsoft.com/en-us/power ... rankcolumn

Andrea