Tables agrégées : du bon usage de SUMMARIZECOLUMNS()


Historiquement nous utilisions la fonction SUMMARIZE() pour créer une table agrégée, en d'autres termes pour créer une table ressemblant aux résultats d'un tableau croisé dynamique, sur laquelle de nouveaux calculs pourraient être appliqués.

Cette fonction prend une table en premier argument (la table dont certaines colonnes seront agrégées), puis les colonnes à agréger (ces colonnes peuvent provenir de la table ou de toute table liée à cette dernière par une relation un à plusieurs).

Exemple : Si nous avons deux tables : 'Produits' et 'Ventes', nous mettrons la Table vente (côté plusieurs) en premier argument de la fonction,puis ajouterons les colonnes dont les valeurs identiques doivent être agrégées, avant de finir par la formule de calcul (Nb : ne pas oublier de préciser un nom pour cette nouvelle colonne, ici 'Quantités vendues')

Quantités vendues par couleur et par fournisseur =

SUMMARIZE(

                            'ventes';

                             'Produits'[Couleur];

                             'Produits'[Fournisseur];

                             "Quantités vendues"; SUM('Ventes'[Quantité])

                        )

 

Pour des questions de performance et de compatibilité, il est préférable de n'utiliser SUMMARIZE(), que pour les colonnes d'agrégat, et pas pour la formule de calcul, cette dernière devant être ajoutée par la formule ADDCOLUMNS() :

 

ADDCOLUMNS(

      SUMMARIZE(

                            'ventes';

                             'Produits'[Couleur];

                             'Produits'[Fournisseur];

        ) ;   

       "Quantités vendues"; CALCULATE(SUM('Ventes'[Quantité]))

 )

En utilisant ADDCOLUMNS() il est nécessaire d'imbriquer notre formule de calcul dans une fonction CALCULATE() uniquement pour assurer une transition de contexte, prenant en compte les valeurs de chaque ligne pour filtrer la Table des ventes.

Dans le cas contraire, la colonne 'Quantités vendues' retournerait une valeur identique pour chaque ligne.

Si vous utilisez Power BI, analysis services 2016 ou Excel 2016, vous pouvez utiliser une nouvelle formule plus simple d'utilisation et plus complète SUMMARIZECOLUMNS() :

SUMMARIZECOLUMNS(

                             'Produits'[Couleur];

                             'Produits'[Fournisseur];

                             "Quantités vendues"; SUM('Ventes'[Quantité])

                        )

Cette fois ci, nous sommes dispensés d'indiquer le nom d'une table et d'utiliser CALCULATE()

Utilisation des filtres :

Si nous omettons la formule de calcul, SUMMARIZECOLUMNS() retourne toutes les combinaisons possibles  de Fournisseurs par Couleur. Ce résultat peut être filtré, par exemple pour ne retourner que les couples Couleur/Fournisseur présents dans la table des ventes :

SUMMARIZECOLUMNS(

                             'Produits'[Couleur];

                             'Produits'[Fournisseur];

 'Ventes'

                        )

 

Absence de contexte de ligne dans SUMMARIZECOLUMNS()

Contrairement à SUMMARIZE() qui dispose d'un contexte de Filtre et d'un contexte de ligne SUMMARIZECOLUMNS() ne dispose que d'un contexte de Filtre.

Par conséquent, si vous souhaitez faire référence à la valeur de la ligne en cours dans votre formule de calcul, vous ne pourrez pas y faire directement référence comme par exemple :

SUMMARIZECOLUMNS(

                             'Produits'[Couleur];

                             'Produits'[Fournisseur];

                             'Produits'[Multiplicateur];

                            "Ventes multiples";SUM('Ventes'[Qté])*[Multiplicateur]

 )

 

Ne fonctionnera pas, car la valeur de multiplicateur existe sur une ligne et qu'il n'y a pas de contexte de ligne dans SUMMARIZECOLUMNS().

Pour obtenir le résultat souhaité, vous devrez utiliser VALUES()

"Ventes multiples";SUM('Ventes'[Qté])*VALUES([Multiplicateur])

VALUES() renvoie la liste des valeurs présente dans le filtre, soit ici une valeur unique par couple Couleur-Fournisseur-Multiplicateur.

 

 

 

 

 

Laissez un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *