Transcription
There's a free tool sitting inside Microsoft's AppSource that most finance teams have never touched. Combine it with Claude and you can build any PowerBI visual you can describe. I've been quietly using this for months. Here's the hack.
If we haven't met yet, I'm Mike. I'm a senior finance leader and over the last decade, I've helped automate more than 150,000 hours out of finance processes. And before we get into it, if you want the prompts, templates, and skills that I use in this and all my other videos, then click the link in the description to get access to my entire AI library. I'll also send you my free weekly newsletter, Finance AI Insider, with tips, tricks, and hacks just like this. So, click the link in the description to get started.
Okay, so here's the situation that I know a lot of you are in. You've got PowerBI connected to your data. The numbers are live. They update when actuals come in. The report does what it's supposed to do, but every single month before the CFO deck goes out, someone on your team is exporting that data into Excel, formatting it there, and pasting a screenshot into PowerPoint. Because PowerBI's native visuals can't do what the CFO actually wants. The KPI cards don't flip red and green based on variance logic. The tables don't cleanly show dollar and percent variances side by side in the format you need. And if someone asks for a bullet chart, the standard visual for tracking performance against budget, well, PowerBI just doesn't have one.
So, there are two free tools sitting in Microsoft's app source marketplace that most finance teams have never touched. When you combine them with Claude, you can build any custom visual you can describe. KPI cards, variance tables, bullet charts, without a developer, without coding, and without leaving PowerBI. I'm going to walk you through three builds today. a red and green variance KPI card, a multilocation variance table with conditional logic applied for each metric, and a bullet chart using a different tool called DAB. By the end of this video, you'll have the full workflow and the exact prompts to replicate all three.
Before we get into the builds, let me show you the foundation. The core tool is called HTML Viewer. It's a free custom visual from Microsoft's App Source Marketplace. That's where you find community-built visuals for PowerBI. What HTML viewer does is take an HTML string from a DAX measure and it renders it as a visual on your report canvas. Now, don't worry if that sounds technical. What it means in practice is you describe the visual you want to Claude. Claude writes the code and PowerBI renders it. You're describing, Claude is building, and the install only takes about 2 minutes. So, let me show you how to get this installed and what it looks like once it's in place.
And here we are in PowerBI. Now, this is a simplified dashboard for teaching before everyone jumps in the comments and says, "Wow, that's an ugly dashboard." Yes, it's about the skill and the process, and any pretty dashboards would be distracting. So, it's intentionally clean and crisp just so you can get the fundamentals in. I know the comments are still coming though. So, let's go ahead and get our new tools installed. So, we're going to come over here to build visuals. Of course, if it's collapsed, if you're fairly new to PowerBI, you just click this button. and we're going to come to the ellipses here, the three dots that says get more visuals. We'll click that. We'll click get more visuals again. And this is going to open up the window that's going to let us get additional visuals. Now, in here, we're looking for app source. Doesn't fully matter, but if your organization has some, they'll be sorted to the top. We can go look just on app source. And we're going to pull in our first app. And for this one, we're going to search HTML. This one will come up. This is what we're looking for. this HTML content. This is not a plug or an affiliate. This is a totally free tool. It's just the best one I found that I use. And we're going to go ahead and install this. So, all we have to do is hit add. And we'll see it installed down here in our custom visual section. And the import was successful.
Now, to use these, they work just like regular visuals. So, we'll click this. It's going to add the visual to the canvas. We'll go ahead and move this over and get it set up. So we'll say we'll put this visual right down here in this convenient blank space. And just like a normal visual, it has a values field and then some granularity. And values is what we're going to use. And that's really it. The setup is that simple. So that's it for the setup. You've got the visual on your canvas. Now the only thing that goes in that HTML content field is a DAX measure. And Claude is going to write that measure for you.
This is one of the most requested things I hear from finance teams. a KPI card that shows the actual, the variance in dollars and percent and flips red or green based on whether you're over or under budget. PowerBI's native card visual cannot do this cleanly, but this workflow can.
Now, here we are in Claude. Now, I want to mention that we're going to be using free features for all of this. You can get really fancy and automate this process if you want to use Claude Co-work or Claude code, but I want to make sure this is approachable and everything I'm going to show you is available on the free version. Now, that includes the model. If you use the really advanced Opus models, the really deep thinking models, those are going to require you to have a paid version. We're going to keep it on Sonnet. The one thing we're going to do is we're going to bump the effort up to high. High being the best effort that we can have on a free plan. You'll see here, of course, it's suggesting Opus. I'm in a paid plan, but I want to show you just the free features because you can do it with all of this.
Now, even with the HTML measure, we have to tell Claude or any AI tool, this will really work on ChatGPT. This will work on Copilot. We're going to use Claude here cuz I found it to be the most capable working with HTML and DAX. Your mileage may vary, but we need to get the information about our PowerBI dashboard into this. Now, again, I've got some great videos. If you do use the paid tools, I've got some great videos. I'll put the link right up here that's going to walk you through how to actually just connect Claude code right to PowerBI and it will just feed all this in automatically. But again, I want to show you the free versions. So, we're going to go to our files and be able to pull out what's called a TMDL file. That's going to give the model all the information. Now, the easiest way to do this is when you build your dashboards, you want to save them as a PowerBI project. The default will just be a PowerBI dashboard. you want to save it as project and that's going to give you the following files. So this is where my dashboard is saved. You'll see this folder called semantic model. The semantic model is the model behind the dashboard. And inside of here, if I go into the folder and go to definition, you're going to see these TMDL models for both the database, the model, and then all of the tables. These files will tell Claude everything it needs to know about your model. All we have to do is attach these and it's going to know your column names, your table names, everything it needs to know to reference them. So, let's go ahead and grab these. We can pull these right over like this. There we go. All right. Then we'll come back. We'll go back and get our database and our model. You see, they're very small files. They're in coding language. It's very easy for Claude to read these. They would look like gibberish if you opened them up, but Claude is going to love this. This is everything it needs to know about our working model as it sits today. And then from there we can go ahead and just prompt this in.
Act as an expert PowerBI designer. I have added a custom visual to my dashboard that will let me create visuals using HTML. I need your help writing the DAX measures to feed that. For the first measure, I want to build a KPI card that can show an actual versus budget variance. The variance in dollars and percent. And the KPI card will flip red or green based on the direction of the variance. Let's do this to start for both revenue and expenses. Higher revenue would be green, lower revenue would be red, higher expenses would be red, and lower expenses would be green. All right, there's our prompt. So, we're telling it how we want it to behave. We're telling it kind of where we are. We're telling it what we want it to do and then kind of some directions about how we want the outputs to behave. So, we'll go ahead and send this off and we'll let Claude work its magic.
So, you see Claude is diving right in. It's reading the TMDL files we gave it to understand. It's going through to understand the data model, the financial data structure. It knew exactly what to do with these files. This is exactly where Claude is strongest. See here, it's going into the relationships and additional context. So once it understands the full model, it's going to learn about our current model and what we have and then it's going to go ahead plan out the KPI card and then write the HTML for us. And just like that, we've got the measures back. Now, again, I could show you how to be really fancy and we could have Claude Code pop this right in, but we're going to stick with the free features. So this is everything that we need to do our measure. We're going to go ahead and copy this. You'll see it's using names right from our data set. We've copied this over. Let's go back to PowerBI. We're going to go ahead and add this into our financials table. We'll do a new measure. We're going to paste this in just like we would any measure. Go ahead and hit enter on that. All right, it is saved with no errors. We're going to go to our custom HTML visual. We're going to go back to our custom HTML visual. And then we're simply going to pull this into values. And just like that, our KPI card has generated exactly like we were asking for. So here's our revenue. It is 2.372 million. There's the budget value. We are favorable to budget. Revenue is higher by 6.4%. Now compare that to what we're getting up here. This is a KPI card of revenue versus budget. It's the same 143,000, but look at the depth. Look at the additional information. How cool is that? Now, just to show you, it did give us four of these and we can swap between them if we want to. Let's go ahead and add another measure. We'll go to new measure. We'll go back and grab DAX for a different one that Claude gave us just to show you how powerful this is. Here's our expenses. We'll go ahead and copy this over. We're going to paste this in. We'll hit enter. Go ahead and collapse that. And then we will simply swap this out. And now for our expenses we're in, we see it flipped the other way. Our expenses were unfavorable to budget. They were high. This understands the logic we gave it that higher is better for revenue, but worse for expenses. You'll see we're $84,000 over in expenses or 15.7%. That's a huge margin hit compared to the revenue growth. And now we've got our KPI.
If this is already clicking for you, go ahead and hit the like button. It generally helps the channel reach more finance pros who need this workflow.
So a single card is a good start, but what finance teams actually bring to manager views is a full table. Every location, every metric, all the variances in one view. Now let's come back to Claude. And I honestly want to give it a lot of leeway in this just so you can see how powerful this truly can be.
Continue to act as a PowerBI expert. I want to build a metrics table that shows comparable metrics across the three different locations as well as for the total business and highlights whether a location is favorable or unfavorable to the overall business average. This should include things like cost of goods sold as a percent of sales revenue, labor as a percent of sales revenue, marketing as a percent of sales revenue, and anything else that you think may be relevant to management. Again, if you're really building this out, you want to be a little bit more prescriptive than this. I'm giving it a lot of leeway to make decisions, but that's the point is so you can see just how powerful this is, how much it can output for you, and then you can kind of tailor it in to your specific business need. We're going to go ahead and send this away. We'll let Claude do its magic. I would be shocked this time if it didn't have a couple of questions for us.
All right, here's some metrics I would like to add. Gross profit, total operating expense, net profit. Now, you'll notice it's asking us to tell us what account values are here. That's because we're when we give it the semantic model TMDL files. We're giving it the structure, but not the actual data. That would obviously be a lot heavier. So, there's a couple design questions for us. Again, I knew there would be some questions. How should this be highlighted when it's unfavorable? Red or green background on the cell? A color dot. I think in this case with a lot of metrics, a color dot would probably be better. The other one might be a little distracting. Should this table show actuals only or actuals versus budget? Let's do actuals cuz these are going to be metrics and KPIs. So, we'll focus on that. And Claude is off to the races writing our new logic. So, this is going to give us our location benchmarking table. I'm assuming there's going to be a lot of code here. So, we'll let it go off, do its thing, and be excited to see what comes back.
And Claude is already back. It's just crazy how fast this writes code. We'll go ahead and copy this. We're going to come back to PowerBI. We're going to add this as a new measure again to financials. All right, we'll go ahead and pop that in. There we go. Check. All good with no errors. We'll close this out. And then we're going to swap this into our KPI card right here. So, here's our location benchmarking. And here we go. So, cost of goods sold 25.8 favorable to 25.9 261 bad. Lower Manhattan 259 right on average. and so on down to net profit which is showing the overall amount the best performing location being Astoria at a 36.12% net profit margin. How cool is that? It not only built the KPI card, it not only built the formatting to show us all of the colors, it built the actual DAX logic to calculate these KPIs. Because you'll see up here, they're not here. We've got revenue versus budget. We've got sales revenue current. We've got sales, revenue, budget. We don't have anything like this. All of that logic is living inside the KPI. How cool is that?
So far, we've covered the KPI card and the variance table. Both of those are built with HTML viewer. What I want to show you next is a completely different approach for a completely different type of visual. One that finance teams use all the time in board reporting. Almost no one knows how to build in PowerBI and it takes only 30 seconds with Claude. And by the way, if you're finding this useful, make sure to subscribe while you're here. I put out new videos every week on exactly this kind of thing, and I don't want you to miss anything.
Bullet charts are the clean way to show performance against target. One bar for actuals, a reference line or background bar for budget, and an immediate read on where you stand. Finance textbooks use them, consultants use them, they're the standard, and yet they don't exist in PowerBI's native visual set. So, there's a second free tool from AppSource called DAB that fixes this. It works a little bit differently than HTML viewer. Instead of writing a DAX measure that returns HTML, you're giving Claude a description of the chart and it writes a specification in a format called Vega-light. It's just a way of describing charts that DAB knows how to render. Same basic idea. You describe what you want. Claude writes the spec. You paste it in. The key thing to get right in the prompt is that your field names have to match exactly what's in your PowerBI model. If Claude guesses and the name is slightly off, the chart renders blank. So, let me go ahead and show you how to install it and then the full workflow.
So, now that we've gone through a couple of examples with this tool showing how we can build HTML charts, which is really good for things like KPIs, analytics, and comparison tables, now we're going to look at a tool called DAB. And this is going to help you build more finance-specific kind of visuals and analyses. This is really going to enhance what PowerBI can do in kind of the more traditional analytics space. And in this case, we're going to build out a bullet chart to look at actuals versus budget. Now, just as we did before, we're going to come to the ellipses. We're going to get more visuals. We're going to go to app source visuals. And then we're going to search for DAB. So, here's our tool for declarative visualization. As I mentioned, this is going to work in a tool called Vega or Vega-light languages, which Claude is very capable of. I don't I don't know a single person who just natively knows or can work in this language, but Claude is going to handle all of this for us. We'll go ahead and add this. Just like before, our custom visual will appear right down here. There we go. Visual was successfully imported. And again, just like before, we add it like a regular visual, and it's going to pop into the space we left. Now, this one's going to be slightly different. So, before we added all of our HTML right to the values field. In this case, what we're going to do is we're going to add the data or the columns we need that Claude tells us we need. Once we've added our data, we're then going to come in here and DAB is going to let us create the visual in here and that's where we'll put in our language.
Continue to act as a PowerBI export. I'm now shifting gears and we're going to work with a cool tool called DAB. DAB, which works in a language called Vega Script or Vega Light. What I'd like to do here is build a bullet chart to show actuals versus budget for each of our three locations. I need you to tell me which measures I need to pull in and then give me the script to add to the chart. All right. So, again, leaving a lot of leeway just to show you how much Claude can do for us here and help us along the way. Your business processes will want to be a little bit more specific, but this is really about the process. So, we'll send this off and we'll let Claude do its magic. And it's coming back and it's saying, "Hey, great shift. DAB is excellent for this. Bullet charts are one of the best uses of Vega Light. So, we're going to go ahead and do that. Now, as I mentioned, we have to pull measures in. So, it's telling us here we're going to need to add in these measures before we can start working on it. So, there's our measures and then we've got the new Vega Light spec. So, this is the coding language. It's already done ready for us. So, we're good to go. We just need to pull our measures in and then we can get to work. So, let's start with our revenue actuals for Astoria. Pop this in. All right, there's one. I'm not going to make you watch me do the other five. Be back in a second.
Okay, back with me now. So, we've got all six of our measures added. We'll go ahead and pull these over into the DAB field. So, we can just pull all six of these in. There's three. There's four. There's five. There's six. Then we can go ahead and edit. All right. All right. So, now that we've got the data, we can come in here and we can create or import a new specification. We're going to create using Vega Light and we want this template to be empty. We'll go ahead and hit create. And then we simply just have to paste it over here. You'll see this code looks very similar to what we just got from Claude. We'll pop down here. We'll grab the Vega. Come back to PowerBI. We'll replace everything there. There we go. That's all set. And then we simply click back to report. And just like that, here is our bullet chart. So, here's Astoria. Here's Health Kitchen, Lower Manhattan, all of which are exceeding budget. And now we can have some very happy executives. It was even kind enough because it works with PowerBI to have our tooltips come up and tell us what's going on. This is Lower Manhattan and here's the exact numbers. We didn't even ask for tooltips, but there they are. How cool is that?
The finance teams I work with who are using this workflow have knocked out the monthly PowerPoint rebuild entirely for several reporting elements. KPI dashboards that used to take hours to format manually now live in the live PowerBI report. Board pack prep has dropped significantly because the visuals update with the data. A controller I worked with at a manufacturing company was building their actual versus budget slide every month as a formatted table. No visualization, just numbers because bullet charts weren't available natively. One DAB build and that slide now lives in PowerBI. Board pack prep time dropped from about 4 hours to 20 minutes for that section alone.
Now, I'm curious. Drop a comment and tell me what visuals have you been manually recreating in PowerPoint every month that you wish lived in PowerBI. I read every comment and I'm always looking for the next build to demo. If you want to go deeper on Claude and PowerBI, this video will show you how to build your entire dashboard to Claude code. You don't want to miss it. I'll catch you over there. Until then, this is Mike signing off from F9 Finance. Cheers.