Speaker 1: In this video, we'll be creating a beginner-friendly Power BI project where we'll look at the Florida Medicaid data from March 2023, where we focus on the company of Humana. So looking at this, so I can just go over this data a little bit more in depth, we have the county for each of the ones in Florida, the state, all Florida, the county seat of each one, the zip code, country, latitude, and longitude of the county seat. The count, and the count would be how many people have Humana in the county for their Medicaid program. MMA total is the total of people in the county that have Medicaid in general, and the population is just the total amount of people in the county. What we're gonna do now is we're going to import this data from Excel. So what we're gonna do is click this right here. Once we have the data uploaded, we're gonna click on this data tab right here, just so we can include this in our data set. However, there's gonna be a couple changes we're gonna make, and we're gonna do that in Power Query. So we're gonna click transform data right here. Now that we're here in Power Query, there's a couple columns we want to make some changes to. So what we're gonna do here is in this county and state column, I want to combine these two. So there's a comma between both, and it'll just be one column. So I'm gonna leave county where it is. I'm going to hold control and click state as well. And then what I'm gonna do is I'm going to right click on the state, and then what I'm going to do now is go to merge columns. And then when I'm at merge columns, it's going to say how do we want to merge it. And for a separator, I'm going to put a comma and the new name. I'm going to just call it county name. Click okay, and we get our brand new column, which is this perfect. So now the state and the county are now in just one. It'll make it much, much easier when we get to the dashboard part. So moving now over to count and MMA total. What I want to do here is I want to find a percentage of the amount of people who have Humana in each of the counties. So what I'm going to do here is I'm going to click count and also control and click MMA total. And I'm going to go to add column and go to custom column. So what it's going to do now is it's going to say okay, what kind of custom formula do I want to create? And I just want to do count. I want to divide that by MMA total. There we go. I'm going to name this column market share. I'm going to click okay. And we get a brand new column over here. So we get all this brand new data. So we see it's 0.22, but remember that this is 22%. So they're saying in this first county that approximately 22% of people who have the Medicaid in Florida in that county have Humana as their provider. So once we're all good with that, we're all good here. We then go back to file and close and apply. Once the data is all loaded, we're going to start by creating our map. So we're going to use the filled map right here. Double click on that. And I'm going to drag in county name to location. So let that load for a second. Okay. So we're good there. And then what I'm going to do now is yep, it's all loaded. So it picked up the state of Florida, right? And if you hover over each one, it picks up on the county. So the only problem is we want to have a separator and we're going to get to that in a little bit. We're then going to move to what do we want to show off here, right? The whole purpose of this project is to show the market share of each county in Florida. So what we want to do next is go to market share and let's go put market share in tool tips. So there we go. And there's one quick thing I want to change here. And that is I'm going to double click on market share and the format. I'm going to change this to one second data type to decimal number. Yes. We're going to say yes for this. Cool. Then we go to here, the format, decimal number. Cool. So we're all good there because now what we could do is go back to the map here. And for tool tip, it says the minimum. No, we just want to get the, let's go here. I'm going to just do the sum of the market share. And now going through each one, we see that the percent is there. All good to go. We're going to fix that a little bit to show it off in a correct way. But for right now, this is exactly what we want to do. Next we want to do is I want to show off the number. So I'm going to do the count, also tool tip. Do as well. Okay. And I also just want the population so we could see population and let's take a look. Okay. So we have everything we want in the tool tip, but now there's a couple of changes we want to make. So going back to market share, double click on that. What I want to do for this is I want to now put this as a percent. So I'm going to click percent right there. Give that a little bit of a second, let that load. And let me hover around after it's all done and it's all updated now. So we see in the tool tip here as I'm dragging across, each of them are now set up as a percent. So I'll leave that there. Next I'm going to go to the count. And for count, I'm going to put in the comma, click comma there, let that load. And as I hover across in a second, you'll see now that there's a comma in the sum of count. And now same thing for the population, go to that, click comma, give it a second. We'll take a look when it's all good. And now we're all good here with the comma. And what I want to do now is I just want to change up these titles to make sure it is the way I want. So for some of market share, I'm going to just change that to just market share, enter. And for count, I'm just going to put Umana members and population. We're going to call that county population. We're all good there. Now I hover over it's more appropriate, but I want to organize this right based off the market share for each of the counties. So I'm going to do now is go to the format part, go to fill colors and click the FX right here in the colors. Now it's going to give us a choice on how we want to base these colors off of. And I want to do it based off the market share percent. So going here, going down to market share, and we're going to say anywhere from zero to 15%, we're going to give it this kind of like this darker red, add in a new rule, anywhere from 15 to 25%, we're going to give it this like light goldish color. And then anything that is 25 to a hundred percent, we're going to give it, let's see more colors. I want to make it a green. So it's more appropriate to the brand. Okay. So that's green. So like I said, it's going to base off the county. So anything that's zero to 15% is red, 15 to 25 is gold and 25 and up will be green. So let's click apply and sorry. Okay. And see how it looks. And there we go. Perfect. So we have our map right here. So it's going to show us right for every single county you see right here. If it's green, that's a good thing because that means Humana has a decent market share in that county. If it's gold, that means it's somewhere in the middle. And if it's red, it's on the below average side. And that's something that we would want to focus on as a company. We want to do next is go to general on this map and go to title. And we're going to call this market share by county. We'll leave that because that's all good. And I'm going to do something that is one of my favorite parts of Power BI is going back to the formatting part, going to general, go to effects, and I'm going to click shadow. I absolutely love this part of Power BI. I think I'm a huge fan of the shadow effect. Definitely makes it jump out a little bit more. So I'm going to leave that there. And now I'm going to do is I'm going to now create the KPIs right here in our filters and continue on with our project. So as I said, I'm going to be creating three different KPIs. So I'm going to use the card visualization right here. Double click that. Okay. I'm going to be creating three different cards. I'll leave one right there for this first card. What I want to do is I'm going to do the total amount of Humana members just in the state of Florida. So I'm going to do is I'm going to bring count, drag it over to the visual and we get this right here. Okay. So we see 813K. So it's, it's rounding off to 813,000. However, I just want, I want to be the whole number. So I'm going to do is go right here to a format visual and go to call out value. And for display units, I'm going to put as none because I want to be the regular number. I don't want it rounded. I just want the number of the way it is. Next one to do is go back to the beginning. And for fields, I'm going to change this to total Humana members, just so it's much more of an appropriate title, going back to format, general effects, and shadow. So we're good there. It makes us a tiny bit smaller. The next part I want to do is I want to find out the total members in Florida who are under the Medicaid program. So I'm going to double click on the card, create that, bring it down a little bit there, bring it up a little bit more. Very good. Looking good so far. And I'm going to bring over MMA total, bring it over there. Same thing, go back to format, call out value, display units, none, going out to general effects, shadow, going back to the home. We're going to call this total Medicaid members for appropriate. And now we're going to do for this one is make sure that we go to, let's see here. We're going to click MMA total and we're going to put in our comma, let that load a little bit, almost there, almost there and good. Cool. So we're all good there. What we want to do for our third card is we want to find the overall market share of Humana members in the state of Florida. So we're going to do is we're going to bring over a third card. So double click, let's make this a little bit smaller, bring it right there. We'll change the size up a little bit. Now, what we want to do is we're going to create a formula up here. So let's go to modeling and new measure. And we're going to do is we're going to call this the, we'll call overall market share is equal to the sum of the count column. We're going to close that and we're going to divide that by the sum of the MMA total one. That's MMA total, close that and let's click enter. And we get this brand new tab over here. So let me bring this into the card over here and see what we got. Let's bring this in and okay, this is gonna be very good. So we get this right now, it's gonna be set as a decimal, but we want to make this a percentage. So we're going to do is we're going to go up to the format over here, change that into a percentage. And there we go, we get our percentage of the total market. So like I said, 17.98% of all people who have Medicaid in Florida have Humana as their choice of provider. Next we want to do is go to format, go to general effects, and then we're going to click on shadow. So it goes through and we are all good with the percentage here. Now I want to do is I'm going to bring this down a little bit. Because I want to add in a slicer for the county name. So I'm gonna go over to now the slicer tab, don't click on that. And nope, don't want to do that. I'll get out of there. Then do slicer. Next and bring over county name into the slicer tab. And I want to go back over to format because I do want to make it this a drop down. So make this a little bit less much more appropriate. And then what I want to do is go to selection. I'm gonna do a select all so we could choose all the counties at once. And I'm gonna go back to general the effects and click yes for the shadow. And we're all good there for that. Now I want to add in a title. I'm going to add in a title. Title, go to insert shapes, rounded rectangle. If you know me, I love my rounded rectangles, especially when I'm using Power BI. Go here and you know, I feel like I want to make this even a little bit less want a little bit more room here. A little bit more room for the title brings down a little bit more. Good bring that down. Bring the map down a little bit. So yep, matched up. Well, there we go. A little bit more. Good. Now I go is go to style. I'm gonna make this a kind of like a green. Let's go with that. Yeah, go with that. And go to text. I'm gonna turn that on. And for this one should call it the March 2023. Call it the Medicaid report. Keep it nice, simple. We don't have to put in Humana. We don't have to put Florida like we kind of know already what we are talking about here. You know, actually, let's just put Florida in here. But we don't put Humana because we're pretending we work for the company. So we don't have to put that in the title. Let's make this a little bit larger. Let's see how much we want put this. And that was pretty good. Let's change that to a black font. Cool. Leave it there. How about a bold? Yeah, let's go with that. Let's go with a bold. For the border color, put that also as black. Let's take a look. Yep, I like that. And now also I'm gonna put in, I'm gonna put an image right here of the company logo. You could do whatever you want. You don't have to. But at least for this, I'm gonna pretend, like I said, that we work at the company Humana, just so it looks a little bit more appropriate. Now that I have my logo, I'm gonna put it right here in the top right corner. Okay. Nope, don't want that. Perfect. And don't want to make it that large. And good. Right there. Okay, so that's good. And just a little checking everything. I'm going to now change the background. We'll put it as a light gray. Go to wallpaper. Very light gray. Take a look. And yeah, we'll leave it like that just right now. Just a little bit easy on the eyes. So as of right now, we have everything that we need. We have the title, the company name. We have the KPIs we're focusing on, the amount of members who have Humana, the amount of members who are in Medicaid, and the overall market share. And then we could see that. That is the average percentage overall. And we could see, okay, what counties are performing the best, which ones aren't. So we could focus a little bit more as a company on our Medicaid program. A situation we have here, though, is if we click on these maps, the color stays in there. However, if I go to the slicer over here and I click, let's say, Bay, Florida, we see that's green here. However, it doesn't fit the criteria. You might take it off. The county is yellow. So we have to create some kind of formula to tell the slicer what exactly each county should be based off the market share for each one. So I just want to show you this formula I have right here, and I'm going to explain it. I'll give you some time to take a look. And I just want to walk through step-by-step on what's going on. So I'm going to call this the slicer measure, since this is the type of measure we want to use for our slicer. And we're just using an if statement here, saying that if the overall market share is more than 0.25, which is more than 25%, we want to be using this color shade, which is the green that we have in this map. And then I put a little note here just so you can see what exactly is going on here. And then we have to put a comma and then put in another if statement. And I have to say again that if the overall market share is less than 25%, but also more than 15%, then we want to use this color shade, which is the light gold we used. And we see that if is between 15 and 25%, and we put a comma and then we put in quotation marks. We don't have to do another if statement because what we're saying is if it's not this, then this is the false AK. If it's not between 15 and 25, then we want to give this color shade, which is the red that we have. So I want to just go in here really quick and make sure we put in our little pound sign hashtags. That's good. And let's run this through and see what we got. Now I want to do is go to the map again and go to colors. And then I want to go to the FX again. And for this, I want to go to field value and what should we based off of? We want to base off the slicer measure that we just created. I'll click that, click okay. And we will take a look and based off what we see here, let's see, make sure everything is okay. And let's take a look, make sure everything's okay here. Let's test this out real quick. Let's see. So we have Dixie. We see that Dixie, that's 25. That should, yes, that is green. That's accurate. That's accurate. That's looking good so far. Another way we could do this too is if we want to create a quick bar chart just to confirm. But as of right now, everything looks exactly as it should. Like I said, anything that was between 0.15 and 0.25, which it should be this gold. And the biggest goal now is to make sure that when we filter that this map here does not get affected. So let me just do a little test run. So let's see, we have, let's go with Broward County right here. Come to Broward, let's see. Yep, that stays green. And if I want to do a multiple value, I just hold control and click around. And I remember, yep, I remember the Bay one. That was the one before, if I'm not mistaken, that Bay was 16%. That should be the gold. But when I clicked the filter over here, it changed to green. So it seems right now that it is doing exactly what it should. And this looks pretty good to me. So we're all set here. And that is our project. Like I said, this is a great beginner-friendly project to get to show off your map skills, but also your KPIs. But also, like I said, you really get to work with, first of all, you just saw DAX. We worked with Power Query. And I'm definitely looking forward to getting into more advanced projects as we come along. I appreciate it so much for watching this video, and I can't wait to see you on the next one. Thank you.