Smart Excel Pivot Table Trick - Choose Your KPI from Slicer (Excel Dashboard with DAX)
Amorosjbk63cdsIf you've used pivot tables and pivot charts in Excel before, you know that you can do something like this: you could create a pivot chart that breaks down total quantity by company. You could even go ahead and add a slicer and break this down further by product, for example. But what if you wanted your quantity to be in the slicer instead? So, something like this: you want the ability to select your KPI, you don't want it to be fixed this way. You can select between quantity, product count, and number of orders by company.
You could actually use any KPI of your choice here. You don't have to fix the KPI, keep it flexible in the slicer this way you can add more interactivity to your reports and dashboards. So, let's see how you can set this up. Here's my data set. I have company, order number, customer, product, and quantity.
Now, first thing I'm going to do is to create a pivot table out of this, but I don't want a standard pivot table. I want to add it to the data model at the same time. Now, this is already formatted as a table. The table is called orders and I'm going to go here and summarize this with a pivot table, but with a twist. So, I'm going to place a check mark to add this data to the data model and click on ok.
Now, I can work with a Power Pivot. So, if I go to the Power Pivot tab here, I can go to measures and add a new measure. What I need to do first is to create the KPI's that I need and then I'll deal with the slicer part. So, for the first measure let's do the number of orders. In this case, all I need to do is to count the rows.
So, I'm going to go with count rows. I'm just going to hold down control and use the mouse wheel to make this bigger so you can see better the table here. The only table I have is the orders table. Close bracket and that's it. Click on ok and that's my first KPI here. Now, ultimately I want to see these by the company. So, let's bring company name to the rows as well. So we can see the number of orders for each company. Now, let's go ahead and add the second measure. So, let's go back to new measure.
For the second one, let's do a distinct count of product. I need the distinct count, and for the column name, I want the product. So, it's right here. Close bracket and that's it. Now, next one, let's get the total quantity. All I need here is the sum function and I just need to sum the quantity column. Close bracket and that's it. I guess I have my different KPI's set up. And of course, you can add the proper number formatting to your measures as well. But because I'm not planning to use them separately but instead, I want to use them in another measure.
I'm going to add my number formatting in the end. Now, as a next step, I want to create slicer selection out of this. So, I'm going to set up a separate table with this. Now, you can also call them different as you want. I'll just give the table header a name. I'll call it view. Let's do a number of orders the same, then we'll just have product count. But this is going to be our distinct count of products. And let's just call this quantity sold. Okay, so now that we have our list, let's format it as an Excel table.
The Paid Traffic Playbook https://warriorplus.com/o2/a/sjnjks5/0
The Hidden Goldmine https://warriorplus.com/o2/a/hhngbjv/0
https://jasonoickle.com/p/DwNloD69/100v-aj-dl/The_100-Visitor_Affiliate_Jumpstart-PLR.zip
