Sommare valore annidati in PowerPivot L’argomento è risolto

[Miky]

Ciao
In una query hoi seguenti campi:
Venditore, Cliente, Data, Importo

Vorrei, in qualche modo ottenere una misura la quale, in funzione di un filtro temporale, quindi per un intervallo di tempo, mi restituisca per un singolo venditore, la somma dei clienti per la quale, la somma dei relativi importi supera una certa soglia.

Ovvero
Un venditore ha 3 clienti e la soglia e 8.
Nel 2020 il Cliente1 ha una somma di importi pari a 10, il Cliente2 ha 4, il CIiente3 ha 1. Quindi il risultato sarà 1
Nel 2021 il Cliente1 ha una somma di importi pari a 10, il Cliente2 ha 14, il CIiente3 ha 11. Quindi il risultato sarà 3
Nel 2022 il Cliente1 ha una somma di importi pari a 7, il Cliente2 ha 6, il CIiente3 ha 5. Quindi il risultato sarà 0
E così avanti per ogni venditore

Alla fine, il risultato dovrebbe essere una tabella del tipo
Nel 2022 (periodo filtrato)
Venditore1 - > 1 cliente
Venditore2 - > 5 clienti
Venditore3 - > 3 clienti

Usando una misura

Codice: Seleziona tutto

=SUMX(Clienti; Clienti[Importo])

riesco ad ottenere il Totale Importo per singolo cliente nel periodo filtrato.

Poi dovrei verificare se il totale supera la soglia e ad esempio marcarlo come 1 altrimenti 0
Poi dovrei sommare tutti gli 1 raggruppati per Venditore

Purtroppo conosco ancora poco le funzioni di PowerPivot, mi basterebbe sapere quali usare, poi in qualche modo trovo il modo di scrivere la misura.

P.S. Usando l'editor di Misure di PowerPivot faccio molta fatica a scrivere funzioni complesse, ci sono altri strumenti piu' comodi?

[Andrea90]

Miky,

Puoi raggruppare i dati in una tabella "temporanea" utilizzando funzioni come SUMMARIZE() e ADDCOLUMNS(). Il raggruppamento dovrà avvenire per codice cliente, e con ADDCOLUMNS() andrai ad aggiungere la colonna che esegue il check richiesto (ovvero importo totale >= del valore soglia).

Una volta fatto questo, dentro la stessa misura, potrai utilizzare SUMX() iterando questa tabella temporanea e sommando la nuova colonna appena creata.

Per editare in maniera "smart" puoi scaricarti il tool gratuito Dax Studio, e collegarlo al tuo file excel (verrà aggiunto un Tab dentro i file di Excel). Utilizzerai DAX Studio per costruire le tue misure e fare le varie prove, quando sarai pronto dovrai però copiare ed incollare a mano il codice DAX dentro excel.

Il vantaggio è che però DAX Studio offre tutta una gamma di strumenti molto utili per chi scrive e testa codice in DAX, pertanto, almeno per me, i benefici superano le complicazioni.

Andrea

[Enrico Galli]

ci sono altri strumenti piu' comodi?

Mi permetto di suggerire anche il tool che ho sviluppato personalmente: non ha ausili come colorazione del codice e suggerimenti (solo un accenno di questi ultimi), ma è integrato in Excel e ha altre feature interessanti come quella di mandare le formule a DAX formatter, duplicare le misure, e "committare" le modifiche senza abbandonare l'editor (quindi più rapido fare modifiche successive e vedere il risultato nella pivot)

https://www.excelswissknife.com/help/ta ... le-misure/



P.S: dovrei aggiornare lo screenshot: è di seimila versioni fa

[Miky]

Allora
Qualcosa ho tirato fuori, ammesso che il risultato sia giusto.
Pero' non ci sono ancora.
Ora ottengo un risultato tabellare, mentre vorrei che sia una misura scalare, ammesso che si possa fare (scusate la rima ) in questo contesto

In Dax Studio (grazie per la dritta)

Codice: Seleziona tutto

DEFINE	
MEASURE Clienti[TotCliente] = 
	-- Sommo gli 1 trovati
	SUMX(
		-- Filtro i clienti con sommatoria importi superiore a X e restituisco 1 per ogni cliente trovato:
		FILTER('Clienti',
		        SUMX('Clienti',
		        	 'Clienti'[Importo]) > 100),	
		       	1)

EVALUATE 
SUMMARIZE(
	ADDCOLUMNS(
		Clienti,
		"Pippo", -- Colonna in cui viene salvato il valore della misura TotCliente
		[TotCliente]),
	Clienti[Venditore],
	"TotClienti",
	COUNTX(Clienti,Clienti[TotCliente]) -- Conto i clienti raggruppati per venditore
)

In DaxStudio ottengo una tabella con 2 colonne con valori a prima vista corretti.
Venditore | TotClienti

Portando tutto in una misura in PP pero' mi segnala errore in quanto il risultato e' tabellare.
E qui mi sono bloccato...

Enrico Galli Grazie, appena risolvo, provo anche il tuo tool. Ora non vorrei aggiungere troppa carne al fuoco.

[Andrea90]

Miky,

Intendevo qualcosa di simile:

Codice: Seleziona tutto

=
VAR TempTab =
    ADDCOLUMNS (
        SUMMARIZE ( Data; [Cliente] );
        "@Test"; IF ( [TotImporto] >= 100; 1; 0 )
    )
VAR Result =
    SUMX ( TempTab; [@Test] )
RETURN
    Result

Dove [TotImporto] è un'altra misura che esegue la semplice SUM() del campo da sommare.

P.s. quando crei il tag code ti compare un'intestazione come questa:

"[code
...
/code]"

Dove al posto dei puntini inserisci il codice DAX. Se vuoi formattarlo per bene scrivi così:

"[code=dax]
...
[/code]"

scrivendo code=dax userai la formattazione prevista per il codice dax, mentre con code=pq utilizzerai la formattazione per il linguaggio M di PowerQuery.

Andrea

[Miky]

Invece,
Usando una misura come questa vedi controindicazioni rispetto alla tua soluzione?

Codice: Seleziona tutto

COUNTROWS(
	FILTER(
		SUMMARIZE(
			Clienti,
			[Cliente],
			"OrdiniGT",
			SUM(Clienti[Importo])
		),
		[OrdiniGT]>100
	)
)

Anche se ne ho compreso lo scopo, non mi sono ancora familiari i contesti di riga e di filtro. E quindi potrei fare degli errori grossolani.
Pero' la misura mi sembra semplice e lineare. Ho provato ad applicarla e sembra rispondere alle mie esigenze.
La sommatoria totale per Venditore viene calcolata automaticamente nella Pivot.

Non e' farina del mio sacco ma solo un riadattamento ad un vecchio topic: viewtopic.php?t=609
E' da qui che avevo capito che SUMMARIZE ritorna una tabella e non un valore scalare

P.S. sta volta spero di aver usato i tag code correttamente

[Andrea90]

Miky,

No non va bene, MAI utilizzare SUMMARIZE per creare nuovi campi.

SUMMARIZE si usa solo per "raggruppare" i campi di una tabella, per crearne di nuovi lo si utilizza assieme ad ADDCOLUMNS().

Andrea

[Miky]

Perfetto
Ho implementato e sembra andare bene
Grazie

Miky

[Miky]

C'e' qualcosa che non mi torna e non capisco dove sbaglio
Usando questa misura ottengo il risultato voluto:

Codice: Seleziona tutto

=VAR Tab =
	ADDCOLUMNS(		
		SUMMARIZE(
			Clienti;
			Clienti[Nome]);		
		"@MyCol";
		IF([TotImporto]>=100;1)
	)
	
var result = SUMX(Tab;[@MyCol])
return result

Pero' a me servono 4 misure simili con filtri differenti.
Le ho create e nella mia tabella pivot restituiscono i valori corretti (a prima vista).
I valori sembrano corretti quando il campo Clienti e' espanso e quindi vedo i singoli 1.
Quando comprimo il campo e quindi gli 1 si sommano:
nella prima colonna ho la somma corretta,
nella seconda non vedo la somma,
nella terza vedo il totale delle prime 3 sommate
nella quarta ho la somma corretta.

Sono io che sbaglio? C'e' un bug o cosa?

[Andrea90]

Miky,

Senza avere un file demo da guardare si fa fatica.

Pero' a me servono 4 misure simili con filtri differenti.

Cosa intendi con soglie per filtrare le tabelle diverse?

Andrea