Smart Excel Pivot Table Trick - Choose Your KPI from Slicer (Excel Dashboard with DAX)

if 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 [Music] 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 kpis 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 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 kpis 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 my table has headers and okay under table design let's call this view next we need to add this to the data table so let's go to the powerpivot tab and add to the data model right so we can see it added right here that's our view table and this is our original order table with the measures that we created before so what we want to happen is to create slicers out of this and now we can because we've added it to the data models if i switch my view to all view i can see the view table is right here and we just have one column called view so i'm going to right mouse click here and add this as a slicer right so i have my slices available but when i click on them nothing happens right because they're not connected to anything as a next step i need to create a measure that's going to grab which slicer i've actually selected so let's go to measures add a new measure and for the table view let's just add it right here i'm gonna call it slicer all i need here is to figure out the value that was selected so it should be identical to this value right here and i can use the values function here this one needs a table name or column name well i just have one column in this table that's called view that's all i need now i'm going to click on ok so i can see quantity sold here so i can see that it works when i click on product count we see the switch but the problem is this when i hold down control and do a multi-select i get this error it tells me a table of multiple values was supplied where a single value was expected it can't put multiple values in this cell so what i need to do is to account for this to make sure that the user only selects a single value so let's go back to the measures let's go to manage measures and let's edit this one and what we're going to do here is add a condition so we're going to say if only one single selection is made and to do that i can use the has one value function i need a column name here my column is called view i'm going to close the bracket so if it only has one value then it should show the values basically it will just show the one value otherwise nothing and we can just skip that argument because by default i'm going to get blank back i'm going to press check formula no error is found and click on ok and close now everything works as before and if i hold down control and do a multi-select everything is blank so this works as expected now most of the work is done next step is to make sure i get that switch so that if quantity sold is selected i only see quantities sold if product count is selected i see product count for that i need another measure so let's go and add a new measure i'm going to add this to the orders table and call this kpi and here for the formula i can use an if function or since we have three different things to account for i'm going to use the switch function the switch function needs an expression so the expression that it's going to look at is this one so it's going to look to see what the slicer measure is so i'm just going to type in slicer then we need to take a look at its value and then give it the result that we want so let's just work through it right here so the first one is called number of orders then we have product count and quantity sold so let's start with number of orders but because this is text i shouldn't forget the quotation marks so number of orders then what i want to see is the number of orders measure which is right here next it's product count and if that is the value i want to see distinct count of product that's my measure let's just start typing it in and we see it here and last we have total quantity but it's called quantity sold and the measure is total quantity okay so let's close bracket let's just double check no error is found and here i'm going to add a formatting to this i want it to be a number this should be a whole number and let's use the thousand separator and okay now i have my kpi number of orders shows me only this column product count this column and quantities sold is the total quantity sold okay so now i can update my table here it's kind of difficult to see everything is squashed together basically i don't need anything from here except my kpi so i can kick everything out just leave kpi in there and you can also safely ignore this message that relationship between tables may be needed in this case there is no relationship between them that can't be anything i'm just using this table for the purpose of the slicers okay so we can safely ignore this now as a last step i'm just going to insert the chart so just click somewhere inside the pivot table go to insert and let's insert a column chart i'll just grab this chart i'm going to hold down control grab this as well press ctrl x go to the reporting tab here and press ctrl v okay so i have my chart and my slicer right here now we can optimize the look of this i don't need this right here i don't need the title i'm going to right mouse click add data labels let's remove the grid lines remove the axis here let's make this bold use the shortcut key ctrl b we can also remove these here so for field buttons let's just hide it all okay so i'm going to give this some space and now i want to bring this and put it over the chart so let's right mouse click and bring it to the front and adjust this so that our slicers are in columns instead of in rows so let's go to the slicer option here and increase the columns to three just expand this so we can see everything better okay so let's bring these up here now i don't want to see this border and i also don't need the title here so let's go to the slicer settings and uncheck the display header and click on ok now this gives us more space now to remove that border here we can quickly go to the slicer section here under slicer style just repeat the slicer that you have so basically duplicate it and for the duplicate version under whole slicer go to format and remove that border so click on none and then okay and okay and of course you can give your slicer a better name when i still click away now it's still there because the format doesn't automatically switch to the one that you created it's still on the old one you have to go and select the one that you just created okay so now we have the number of orders i can switch my view to product count or to quantity sold so i've made my kpi selection dynamic another thing i can do is to sort this automatically in the sort order of my choice i can go from largest to smallest and this way i can see that for product count the highest number of distinct products is sold by meta creations in terms of number of orders is bold night and quantity sold it's also bold night that's the deck slicer trick you can use to get more functionality out of your slicers but if you're completely new to dax this probably wasn't easy to follow so i'm gonna add some links below the video to help you get started with dax also let me know in the comments if you are already using powerpivot in your reports that's it for today be thanks for watching and i'm gonna see you in the next video [Music]

Channel: Leila Gharani

Views: 122,806

Rating: 4.9664803 out of 5

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, dax, data model, excel dax measure, hasonevalue, write dax, slicer, excel dashboard, pivot table, pivottable trick, kpi in pivottable

Id: Q3YY1Ue9j-k

Length: 12min 38sec (758 seconds)

Published: Thu Sep 09 2021