Calculer des remises Prorata avec Power Pivot

Nous allons aujourd’hui découvrir comment calculer des remises Prorata avec Power Pivot en utilisant notamment la fonction ALLEXCEPT.

Pour concrétiser la démonstration, j’ai choisi un exemple que je rencontre régulièrement chez mes clients, la gestion des remises pied de page qui sont souvent gérés comme un article spécifique.

Dans le tableau ci-dessus chaque ligne correspond à une ligne détail d’une facture, à savoir la vente d’un produit.

Dans le cas où une remise pied est effectuée, le client utilise un article avec la référence Article REM1 avec une quantité égale à -1 et le montant de la remise en Prix Unitaire.

Pour faire la somme du Chiffre d’affaires ou un calcul de marge global, cela ne pose pas de problème, la remise se déduit du total.

Par contre si je veux faire le total du chiffre d’affaires d’un article ou calculer une marge détaillée par article, il me faut alors faire un calcul au prorata du chiffre d’affaires par article.

Nous allons donc découvrir comment faire cela avec Power Pivot.

Nous rencontrons souvent la même problématique avec des frais de port ou d’escompte pied de page, où il faudra également effectuer une allocation.

J’ai ajouté ma table exemple à mon modèle de données Power Pivot.

Je crée un premier champ calculé Remise ou j’isole la valeur de mes remises en ne sélectionnant que les articles avec une référence égale à REM1.

Remise := calculate(sum(Tableau1[Total]);Filter(Tableau1;Tableau1[RefArticle]= "REM1"))

Sur le même principe, je crée un champ calculé pour obtenir le CA hors remise

TotalCaHorsRemise := calculate(sum(Tableau1[Total]);filter(Tableau1;Tableau1[RefArticle]<> "REM1"))

Je crée ensuite un champ calculé où je vais totaliser mon champ remise pour chaque facture, cela me permet de gérer le cas ou plusieurs lignes remises seraient effectuées, et pour cela j’utilise la fonction ALLEXCEPT une première fois

TotalRemiseParFacture := calculate([Remise];ALLEXCEPT(Tableau1;Tableau1[NumFacture]))

Je fais de même pour obtenir mon CA hors remise par facture en créant le champ calculé suivant :

TotalCaHorsRemiseParFacture := calculate([TotalCaHorsRemise];ALLEXCEPT(Tableau1;Tableau1[NumFacture]))

Maintenant que j’ai mon CA par facture et mon total remise par facture, je vais calculer la part de la remise appliquée à chaque article de façon à obtenir un CA net de la remise par article.

Pour cela je crée le champ calculé suivant :

RemiseProrata : = [TotalCaHorsRemise]*[TotalRemiseParFacture]/[TotalCAHorsRemiseparFacture]

Et ensuite je crée un champ calculé pour calculer mon TotalCaNet

TotalCaNet : = [TotalCaHorsRemise]+[RemiseProrata]

la valeur de la remise étant négative, elle vient bien se déduire du total Ca Hors remise

Je crée ensuite le tableau croisé dynamique suivant, où vous pouvez vérifier que le CA net de chaque article tient compte de la déduction de la remise.

Pour ceux qui préfèrent, je vous joins une vidéo.

Joyeuses Fêtes