How To Create A Matrix (BvA)

Overview

This video teaches you how to build a Budget vs. Actual matrix. It covers setting up the underlying query, formatting cells and headers, and adding calculated variance columns using Excel-style cell formulas. It also shows how to add filter sets with default values so the matrix loads with sensible data on first view, and how to parameterize the query so those filters actually drive the results.

Facts to Remember

• Clicking any cell in a matrix opens the editor for the space that produced it.
• A filter set's default value only affects what's shown before a user makes a selection.

Transcript

Hi everyone, my name is Rahim, here from the product team at Una AI.

And today we're going to be going through how to build a matrix.

So we're going to be building a budget versus actual variance matrix for APEX spending.

So let's get started with that.

So in our Explorer, in our main folder, I've created a folder called a walkthrough.

I'm going to create another dashboard inside of it.

I'm going to call it.

Budget versus actual OPEX funding.

Now we're in our dashboard.

I'm going to click add widget.

I'm going to click matrix.

Now we have a matrix.

Perfect.

I'm just going to zoom in a little bit and make it larger.

Perfect.

As we can see, we can add a data set, which is what we're going to do.

Before that, I'm just going to name this matrix budget versus actual OPEX spend.

Perfect.

Add data set.

Okay, so for the data that we want to bring in, I'm going to put under filters, measure, scenario under columns and period.

And then the account is going to go under rows.

That's how I want it to be formatted.

For measure, the data sits in venue.

So I'm going to bring that in.

From scenario, I'm going to bring in actual and plan.

And then for period, I'm going to bring in 2026 and 2027.

And because we are bringing in two periods and two scenarios, I'm going to directly join them.

And then from account, we're going to go into OpEx, but I'm going to find my general operating expenses and bring that in and do self and descendants.

And then I want the total general operating expense at the bottom.

So I'm going to post order, which is going to do that for us.

And to see if the data is loading properly and how I want it, I'm going to click the preview button down here.

And I'll be able to see this is exactly what I was hoping for, an actual and planned for 2026, 2027 column with the rows of these accounts that I wanted to see and then the total general operating expenses that's added up because the post-order toggle on that we did.

Okay, perfect.

I'm going to click close preview and I'm going to hit okay.

And this is the matrix so far.

And again, if we want to make it a bit larger and see more accounts, we can make it wider or increase the length of it.

So it looks good for now.

Now I want to do some formatting.

In order to do some formatting, what I'm actually just going to do is you're able to click on any cell that you want.

And once you click on a cell, it will basically open up the related.

Now, if I click actual, actual is a column header, right?

This cell that has actual in it is a column header inside the data set space.

So if I click actual, we will see column headers comes up and we are in the data set space automatically.

Now, if I just make the background color gray.

It will make the column headers inside the dataset space gray, which is exactly what I wanted.

We can give it a font weight of about, let's say, 500 to give a little bold touch to it.

Perfect.

Now, if I was to click this cell, which is the rows, which is the row header, which is in the dataset space, same space as this.

If I click on it, it will make the row headers, number one over here, which I can edit under the dataset space.

So if this was a different space, that space will load up and its equivalent section will load up.

So for row headers, I will do the same thing.

500.

Perfect.

Now it is formatted nicely the way I want it.

Now what we're going to do is we are going to add the variance column that we want to add.

The way to do that is at the bottom here, you will see an add button.

A plus sign, you will click add column and you will see that it has automatically loaded us up into the space of the new column that we created and we can see our new column being generated over here.

Perfect.

Now I'm going to add a cell.

Once we click add cell, this is basically talking about the first cell over here.

The first cell, I'm going to stay compliant with the previous column header design and make it gray, the same color.

Perfect.

And then I'm going to click.

Add cell at the bottom here, which will be the second cell.

And for the second cell, I'm going to call it variance with a dollar sign.

And then no formula yet because the formula will come in the third cell.

And then again, just making the same color, giving it a font weight of about 500.

Perfect.

I'm going to add a third cell.

The third cell will be this one.

Now this is where we want our formula.

Our formula will be same as Excel.

So it's C3 minus B3.

Very, very similar process.

So I'll do C3 minus B3.

And then I'm going to toggle on this extents button, which will basically extend the formula and apply it to this whole column, the respective rows for it, which is exactly what we want.

Perfect.

And then the style is we can put as, for example, currency.

And it is USD.

If we want to enlarge this, for example, like make it bigger because we're seeing some dots and we're not able to see everything, we can just slide it and make it neater.

Same with this.

We'll just keep it at that for now.

And then what I can do is just make this bigger from the bottom, which is what we can do.

Perfect.

But for now, I'm just going to shrink it back just for this video so we can add another column.

So perfect.

I have my variance in dollars amount.

I have my actual plan.

Perfect.

Everything's looking good.

Now we're going to add another column for the percent variance that we're going to want to calculate.

So again, I'm going to go back.

I'm going to have that plus button, add column.

Perfect.

And I have that column here now.

And I'm going through the same process of adding a template, adding a cell in it, which is going to be the first cell, right?

Making that gray.

Then adding the other cell, making that gray, and calling that.

Variants with the crescent sign.

And then we're going to give that a font weight of 500.

And then the third cell will be the formula, which will be C3 minus B3, same formula as Excel, very, very similar.

And that will give me my number.

And I will click extends again, so it applies to the whole column.

Perfect.

And as we can see right now, it's in decimals.

That's because, again, we're going to go down and click style and then click percent.

And there we go.

That is how we wanted it.

And perfect.

So now we have our both our variance columns.

We have the actual and plain column.

Now, for example, what I was talking about before, if we right click here and click edit and I was to, let's say, click actual, that cell right there or any of these cells.

It will take me to the respective space of it, which is the data set space, right?

And it will show me that column headers because actual is a column header.

Same if I was to click with a number, this exact number is coming from the data set space and then it is data, so it brings that section up.

If I was to click variants, we could assume before I click it that this would, that should open up the space of the column.

And if I click it, it will open up that column space that we created.

Same with this.

This will be the second column that we created, which will be a different column, and it will bring us to that space.

Perfect.

Now we want to sort of touch up on this matrix more, make it a bit more functionality to it, make it more customized.

So what we're going to do is we're going to start adding filter sets, which will allow us to let this dashboard dynamically update if we were to want to see, for example, different years or different scenarios, right?

So we're going to add two filter sets.

In the first filter set, we're going to come and we're going to bring in scenario and period.

I'm just going to click bring it in.

I'm going to click period.

I'm going to click in this arrow.

It's there.

I'm going to click scenario.

I'm going to make sure it's mandatory so the user has to select one.

And then I'm going to scroll down and it says default value.

I'm going to click.

Dimension query.

Now, the reason we go on default values is if you create a filter set and you don't use the default value section, everything will work perfectly fine.

It's just that once you first initially load up the dashboard and you log into this dashboard and you log into this dashboard that we created, right, the budget versus actual OPEX spending, it will not display anything until you pick the filters like, okay, I want actual and planned for 2026, 2027.

Then it will display the Topics include the Model Builder, Explorer, dimensions and hierarchies.

Topics include the Model Builder, Explorer, dimensions and hierarchies.

Topics include the Model Builder, Explorer, dimensions and hierarchies.

Topics include the Model Builder, Explorer, dimensions and hierarchies.

Okay, so I'm going to click on the default value for scenario.

I'm going to click dimension query.

I'm going to click edit query.

And then what I'm going to do is click scenario.

And I'm going to, for example, just bring in actual.

And then I'm actually, sorry, also going to bring in plan.

And then for, or plan would be the next one.

So we're going to go back and remove plan because this is the first filter set.

So we'll just leave it at actual.

And then for period.

I'm going to make sure it's mandatory, click dimension query, click edit query, select period, and then just bring in 2026, hard-coded, right?

Only for the default value, and then click save.

Now, again, look, I'm able to have that functionality where I can choose if I want it to be actual plan or AI forecast, what-if sections, or any year that I want.

But when it loads up, it will just display this automatically right away.

If we didn't make default values, this would just be blank.

And maybe I don't want that, right?

So we're going to do the same thing for the second filter set.

We're going to bring in scenario.

And then we're going to bring in period.

We're going to make sure it's mandatory.

We're going to go on default value.

Actually, let me quickly show you what would happen if we didn't click default value.

So scenario and period are mandatory.

Perfect.

See, now we're still able to go here and select the value that we want.

And once we do that, it will display.

But initially, once we first loaded it up, it looked like just this.

So it was requiring us to select it.

But for this, it's already hard-coded and selected initially.

But we still can change this as well.

So that's the difference.

So we're going to go back and make scenario default value.

We're going to give it plan.

Save for period.

We're going to just give it dimension query again.

We're going to give it period and we're going to bring in 2027.

Perfect.

And then we're going to save that.

Perfect.

Now it loads up like this every single time.

Perfect.

And as we can see, our values are good.

These two values are still hard-coded in our data set, so we're going to quickly go ahead and change that.

So we're going to click Edit, we're going to go on our data set, we're going to click Edit Query, and instead of having scenario and period hard-coded to actual and planned in the data set, so it's always going to bring up actual and planned, it's always going to bring up 2026 and 2027, regardless of what metrics we use in our filter set, we're going to quickly change that.

So for scenario, we're going to delete both, and we're going to write Topics include the Model Builder, Explorer, dimensions and hierarchies.

Topics include the Model Builder, Explorer, dimensions and hierarchies.

Topics include the Model Builder, Explorer, dimensions and hierarchies.

Topics include the Model Builder, Explorer, dimensions and hierarchies.

Topics include the Model Builder, Explorer, dimensions and hierarchies.

Topics include the Model Builder, Explorer, dimensions and hierarchies. parameter.period.0.k which will be the period value with the first filter set that's what the zero represents parameter.period.1.k which is the period value for the second filter set which is what the one represents perfect and now if i click ok and i go back now these won't be hard to put in 2026 2027 now they will dynamically change with the years that i choose so for example if i was to choose Actuals for 2026 and also plan for 2026.

You will be able to show me that.

Obviously, right now we don't have data for 2026, but you will be able to choose your years and it will display that data for you based off of your filter sets.

And yeah, that is how you create a matrix in Una.

Thank you.

Related Articles

• Matrix — the matrix widget reference
• Matrix Widget: spaces & layout — how rows, columns, and spaces are defined
• Model Queries — the query that feeds the matrix