Commutation des calculs dans un tableau croisé dynamique avec des segments

Les segments dans les tableaux croisés dynamiques peuvent être utilisés non seulement de manière classique - pour filtrer les données source, mais aussi pour basculer entre différents types de calculs dans la zone de valeur :

La mise en œuvre est très simple - tout ce dont vous avez besoin est de quelques formules et d'une table auxiliaire. Eh bien, nous ferons tout cela non pas dans le résumé habituel, mais dans le résumé construit selon le modèle de données Power Pivot.

Étape 1. Connexion du complément Power Pivot

Si les onglets du complément Power Pivot ne sont pas visibles dans votre Excel, vous devrez d'abord l'activer. Il y a deux options pour cela :

• Languette promoteur - bouton Compléments COM (Développeur — Compléments COM)
• Fichier – Options – Compléments – Compléments COM – Aller (Fichier — Options — Compléments — Compléments COM — Aller à)

Si cela ne vous aide pas, essayez de redémarrer Microsoft Excel.

Étape 2 : charger des données dans le modèle de données Power Pivot

Nous aurons deux tableaux comme données initiales :

Le premier est un tableau avec les ventes, selon lequel nous construirons plus tard un résumé. La seconde est une table auxiliaire, où sont entrés les noms des boutons de la future tranche.

Ces deux tables doivent être converties en "intelligentes" (dynamiques) avec un raccourci clavier Ctrl+T ou équipe Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau) et il est souhaitable de leur donner des noms sains sur l'onglet Constructeur (Conception). Que ce soit, par exemple, Ventes и Services.

Après cela, chaque table doit à son tour être chargée dans le modèle de données - pour cela, nous utilisons l'onglet PowerPivot bouton (dans la fenêtre de contrôle qui apparaît maintenant) Ajouter au modèle de données (Ajouter au modèle de données).

Étape 3. Créer une mesure pour déterminer le bouton appuyé sur la tranche

Les champs calculés dans un tableau croisé dynamique par modèle de données sont appelés les mesures. Créons une mesure qui affichera le nom du bouton enfoncé sur la future tranche. Pour ce faire, dans l'un de nos tableaux, sélectionnez une cellule vide dans le panneau de calcul inférieur et entrez la construction suivante dans la barre de formule :

Ici, le nom de la mesure vient en premier (Bouton enfoncé), puis après deux points et un signe égal, une formule pour le calculer à l'aide de la fonction VALEURS DAX intégré à Power Pivot.

Si vous répétez cela non pas dans Power Pivot, mais dans Power BI, les deux-points ne sont pas nécessaires et à la place VALEURS vous pouvez utiliser son homologue plus moderne - la fonction VALEURSELECTIONNEE.

Nous ne prêtons pas attention aux erreurs dans la partie inférieure de la fenêtre qui apparaissent après avoir entré la formule - elles surviennent, car nous n'avons pas encore de résumé et de tranche dans laquelle quelque chose est cliqué.

Étape 4. Créer une mesure pour le calcul sur le bouton enfoncé

L'étape suivante consiste à créer une mesure pour différentes options de calcul en fonction de la valeur de la mesure précédente Bouton enfoncé. Ici la formule est un peu plus compliquée :

Décomposons-le morceau par morceau :

1. Fonction INTERRUPTEUR - un analogue de IF imbriqué - vérifie le respect des conditions spécifiées et renvoie des valeurs différentes en fonction du respect de certaines d'entre elles.
2. Fonction VRAI() – donne un "vrai" logique pour que les conditions vérifiées ultérieurement par la fonction SWITCH ne fonctionnent que si elles sont remplies, c'est-à-dire la vérité.
3. Ensuite, nous vérifions la valeur de la mesure du bouton enfoncé et calculons le résultat final pour trois options différentes - comme la somme du coût, de la vérification moyenne et du nombre d'utilisateurs uniques. Pour compter les valeurs uniques, utilisez la fonction COMPTE DISTINCT, et pour arrondir – ROUND.
4. Si aucune des trois conditions ci-dessus n'est remplie, le dernier argument de la fonction SWITCH est affiché - nous le définissons comme factice à l'aide de la fonction VIDE().

Étape 5. Construire un résumé et ajouter une tranche

Il reste à revenir de Power Pivot vers Excel et à y construire un tableau croisé dynamique pour toutes nos données et mesures. Pour ce faire, dans la fenêtre Power Pivot sur La principale commande de sélection d'onglet tableau récapitulatif (Accueil — Tableau croisé dynamique).

Puis:

1. Nous jetons le terrain Produit de la table Ventes à la région rangées (Lignes).
2. Lancer un champ là-bas Résultat de la table Services.
3. Clic droit sur le champ Résultatet choisissez une équipe Ajouter en tant que tranche (Ajouter en tant que trancheuse).
4. Lancer la deuxième mesure Conclusion de la table Services à la région Valeurs (Valeurs).

Voici, en fait, toutes les astuces. Vous pouvez maintenant cliquer sur les boutons du slicer - et les totaux du tableau croisé dynamique passeront à la fonction dont vous avez besoin.

Beauté 🙂

• Avantages du pivot par modèle de données
• Analyse de plan-fait dans un tableau croisé dynamique sur Power Pivot
• Créer une base de données dans Excel à l'aide du complément Power Pivot