Dans le cadre du concours ModelOff 2013, 16 questions ont été posées aux candidats au sujet d’une base de données (en plus de toutes les autres questions portant sur d’autres sujets, évidemment!). Cet article a pour objectif de partager avec vous, deux de ces questions, portant sur cette base de données et surtout, vous fournir les étapes à suivre pour répondre adéquatement à ces questions.
Base de données de départ
Pour répondre aux deux questions suivantes, vous devez vous référer à une base de données, dont voici un extrait:
Question 1
En ne considérant que les codes postaux 3013, 3017 et 3031, quels items ont réalisé le plus de profits en février?
Résolution Question 1
Pour répondre à cette question, vous devez construire un tableau croisé dynamique, à partir de la base de données ci-haut. De préférence, vous devriez d’abord mettre la base de données sous forme de tableau. Pour savoir comment mettre la base de données sous forme de tableau, vous pouvez relire l’article Excel: Mettre sous forme de tableau. Vous pourriez également utiliser les filtres mais il est fort à parier que dans ce cas, vous mettriez plus de temps à répondre aux questions. Ça demeure néanmoins une option.
Étape 1: Comme la base de données ne comprend pas de colonne avec les profits, vous devez l’ajouter. Vous pouvez le faire de deux façons: soit vous insérez un calcul personnalisé dans votre tableau croisé dynamique, soit vous ajoutez une colonne de calculs à votre base de données. Si vous souhaitez insérer un calcul personnalisé dans votre tableau croisé dynamique, je vous invite à relire l’article Insérer une fonction personnalisée dans un tableau croisé dynamique. Sinon, vous pouvez simplement ajouter une colonne à votre base de données, avec le calcul suivant:
-
- Ventes = (# unités vendues * prix de vente unitaire)*(1-% de rabais)
- Coûts = # unités vendues * coût unitaire
- Profits = Ventes – coûts
Étape 2: Convertissez les jours en mois dans votre tableau croisé dynamique. Pour ce faire, vous devez utiliser la fonction “grouper”. Pour savoir comment utiliser la fonction “grouper”, vous pouvez relire l’article Tableaux croisés dynamiques dans Excel: 3 fonctionnalités sous-utilisées.
Étape 3: Une fois que vous avez ajouté votre nouvelle colonne, soit celle des profits, et que vous avez transformé vos jours en mois, vous pouvez construire un tableau croisé dynamique avec les paramètres suivants:
-
- Filtre sur les codes postaux: ne retenir que les codes postaux: 3013, 3017 et 3031
- Filtre sur la date : ne retenir que février
- Codes d’items dans les lignes du tableau
- Somme des profits dans la section des valeurs
Étape 4: Vous pouvez trier les données en ordre décroissant et retenir la première valeur ou faire le top 1 des plus grandes valeurs. Dans le premier cas, attention de bien trier les données sur la colonne des profits et non sur la colonne des codes d’items. Dans le deuxième cas, attention de bien choisir le “top” 1 des items selon la somme des profits.
À la fin, vous devriez obtenir la réponse suivante: Code d’item 17.
Question 2
Placez les vendeurs en ordre décroissant, basé sur le nombre de factures qu’ils ont émises pendant le mois de mai.
Résolution Question 2
Attention, cette question est une attrape! Pour obtenir la bonne réponse, il faut faire gaffe aux numéros de factures puisqu’on peut retrouver plus d’une fois le même numéro de facture dans la base de données.
Étape 1: Ajoutez une colonne à votre base de données pour indiquer les numéros de factures uniques. Pour ce faire, vous devez utiliser la fonction Nb.si (Countif), imbriquée dans la fonction Si (If), tel qu’illustré dans l’article Nombre de données distinctes dans une base de données.
Étape 2: Construisez le tableau croisé dynamique comme suit:
-
- Filtre sur la date : ne conserver que le mois de mai
- Vendeurs dans les lignes du tableau
- Somme des valeurs distinctes dans la section valeurs
Étape 3: Triez les données par odre décroissant, sur la somme des valeurs distinctes, pour obtenir:
.
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Pour la question 2, il est plus efficace d’utiliser la mesure : NbreFactEmises=DISTINCTCOUNT(BasesDeDonnees[Invoice Number])
Bonjour Sophie Marchand
Dans la question 2 toujours; il y a une erreur dans la formulation du problème : c’est plutôt Mai et non Février.
Bonjour Mario-Charly,
En effet, j’ai remplacé mois de février par mois de mai.
Merci!
Sophie
J’adore ce que tu fais, je suis un amoureux fou de la BI aussi.
LA première chose que je fais le matin une fois au bureau : c’est aller sur le CFO masqué
En utilisant systématiquement des colonnes calculées; on introduit un row context qui en matière de performance est moins efficace que la mesure qui elle est plutôt évaluée selon un Filter Context.
Bonjour Mario-Charly,
Ce serait une préoccupation, en effet, si l’objectif était de mettre en place un outil. Par contre, ici, l’objectif était de répondre à 16 questions en 40 minutes sur une base de données. Dans ce contexte, je crois qu’il est plus rapide d’utiliser l’approche que je propose ci-haut. De plus, c’est ce que suggère ModelOff dans sa question, soit utiliser des TCD et insérer des colonnes de calculs dans la base de données.
Au plaisir,
Sophie
Voilà;
j’ai réussi ton texte le doigt dans le nez.
Voilà;
j’ai réussi ton test le doigt dans le nez.
Bonjour Sophie
Est ce que je pourrais avoir les autres questions?
Bonjour,
Pourrais je recevoir les 16 questions et leurs corrigés par mail.
Bonne journée,
Cordialement.
bonjour
je ne trouve pas le même résultat sur le 1er tableaux croisé dynamique et je ne vois pas où je fais l’erreur ?
je trouve item 44 pour 4398.9