Rock U - Reporting - BI Financial Transaction Report

Transcribed Video Content

Okay. So in this video what you want to do is walk through how we created the financial transaction report in the Power BI template. Now we know you're gonna be doing something completely different with your reports. I think by watching us, create this report, recreate this report, it'll give you a jump start in making your own reports. So let's go ahead and take a look. So if I wanna create a new page in my report, I just click this, plus sign and then we'll give our page a name. So we'll call it financial transactions two. And now I have one of the most intimidating things in Power BI and that's a blank canvas. So, never fear what we're gonna do is just kind of walk through some steps. So I know I wanna create a report transactions. So let's find the right fact table for that. And then case, in this case will be an analytics fact financial transaction. And what I'm gonna do is I know I wanna make a chart that has the amount. So let's go ahead and drag that out. And Power BI tries to help me out and says, okay, based on that data type, what might you be trying to do? And it thinks I wanna create a bar chart which is really close. So that gives me a bar chart of all the giving. I wanna see the the giving by date. Okay. So I'm gonna find the dimension table that would have that information. So in this case, it's analytics dim financial transaction date. And there's a lot of ways I could do this, but the simplest way is just to take this date field and drag it out to my chart. Boom. Alright. Well, that's pretty much exactly what I wanted. Okay. Now, Power BI is very smart with these charts. So I can drill down, through, this data. So if I wanted to see it by quarter, I can just drill down through the hierarchy and I'll actually see it by quarter. Now, if you're observant, you would note that these quarters are not in order and that's not so helpful until you realize they're in in order by this the amount, the sum of this. Now, if I don't want it that way, I can just hit these little three little boxes here and I can say sort by quarter. Okay. Now, it's sorted by quarter but it's sorted in reverse order. Never fear, just keep tweaking with that. And now I have it in the right order. So it's q one, q '2, q '3, q four. The same thing, I can go all the way down through my months. And because I chose quarter that way, it's gonna assume I want months that way and we are good. Now it's really important to understand though that, this is basically showing me all of the January's of all of the all of the years. So this isn't a specific year. Okay? So there's a few ways I could achieve that. The probably the best way to achieve that is to put what's called a slicer at the top by year. Okay? So I'm still here in my financial transaction date model and I'm gonna find calendar year and I'm gonna drag it out here. And it says, okay, years that that's a number. I'm gonna sum that number for you. And that is not helpful because I want to create a slicer. So all I need to do is come over to my visualizations. I'm gonna change the default that it picked for me into a slicer. Okay. And that's what this little funnel is. So I do that. There we go. And now I can go ahead and filter just for 2015, '20 '14 and such. So that's great. That works. That's it. That's that's kind of what I wanted. It's not quite the format I wanted. So in this case, I'll make sure I I highlight this visualization. I'll go to its format settings. And there's a ton of different, options here. I want to turn my orientation into horizontal. There, that looks a lot better. We'll kind of reorganize that a little bit. Drag it up here. I also don't really this calendar year here. So I'm going to click on that. Go back to my styles. I'm going to turn off the header. There. That looks pretty much what I want. I'll go unclick that. And the next thing I want to do is I want to give this report a title. So I'll click on the text box and we'll just give it a title. Okay. Then I'm going to highlight this text. I'm going to bold it. I'm going make it big. I'll make this long. And a lot of times you spend just a lot of times just moving things around and adjusting them and getting them just the way you want them. Okay. Alright. So we're we're making good progress. Now, thing I might to do, let me go ahead and drill back up, is I might want to filter also and slice also on accounts. Okay. So I find my dimension table that deals with accounts, which is DIM financial account. And I'm just gonna drag the name column out. Now it goes, okay, you must have wanted a list of of accounts, which is close. But I actually want this to be a slicer too. Okay? So I'm gonna go back over here and I'm gonna choose slicer. And now I have my slicers. Now, I may not want every single one of these accounts. Okay. So I can actually filter on that. So what I'm gonna do is I'm gonna choose to filter and show only the public ones. So I'm gonna drag out a page level fill oops. First, I have to oops. There we go. Okay. I I gotta select this. This is what I wanna be filtering. So I'm gonna drag out public status as a page level filter right here. I'm gonna choose, I only want public ones that show here. Okay. Now, so now it's filtered out. Okay. So that's really helpful. That's actually allowing me to pick certain funds. Now, another one you might want to do is to filter by a parent fund. So that allows you just to see a very smaller, a small subset of these. Also, , I don't really this name. It doesn't have very descriptive. It's name name of what. Okay. So I'm gonna turn off the header and I'm gonna turn on the title and I'm gonna type in my own title which is account name. Okay. So now I have one filter in. Let's go back to our original and just see what else we have to do. Okay. So what we're pretty much missing here is just these pie charts. And these are super simple. Okay. So the first one is transaction source. The second one is transaction type. So let's go ahead and make these. Okay. So transaction source. Transaction source is actually gonna be on the fact model. And that's probably gonna be the hardest thing in the beginning is just to kind of figuring out what where the the data is. But always start at the fact table. So we said transaction source and then drag that out. Okay. And this is, oh, you must want a list of transaction sources. And that's about half of it. So now, I wanna make a pie chart. So I need to take account of how many of how many, are bank checks on-site collection a website. And so most models have this attribute called count. Now, count is basically gonna be the number one for every single row. And that's helpful because it allows us to count based on that. Okay. So I'm gonna drag the count over here and now I get a numerical view of that data. And so that's super close but I want it to be a pie chart. So I make sure this is highlighted and I come and choose pie chart. Boom. Exactly what I wanted. Now, there's a million different ways I can format this, but I just wanna show you that you can choose, and very easily, update this title. So again, I go under styles, go to title, and I can just change this. Boom. Alright. Pretty easy. Let's continue to do that. I believe the next one is transaction type. And so we're just gonna do the same exact thing. Transaction type with count pie chart. And then it's up to us to kind of format that. Okay. I'm not gonna do the title. You've seen how to do that. Next one is scheduled transaction transaction and then currency type. Okay. So scheduled transaction is going to be schedule type with account and a pie. Okay. And I think the last one was currency type. So let me just make sure that that's what it was. Yep. Currency type. Great. Currency type, count as a pie chart. Alright. There we go. Now what's really cool about this is all of these charts are all linked together on this report. It automatically links them. And so I can start doing things I just wanna see all the on-site collections and see how everything updates. So I can see by year, , the the darker is those that are on-site collections. I can see which of these transaction types it was. I can see if it was scheduled or not and if it was a if it was a check or not. So it allows you to do all kinds of of just clicking around and seeing what's what the values are. So now I'm looking at the data for 2015 and and seeing these charts by that. So really cool. I think once you get in here and start playing, you're gonna find that once it clicks, it's pretty easy and it's actually a little bit fun. So we'll continue on making some of these other reports in in other videos.