Today, let's talk a bit about Calculations aka Formulas, which are called Measures in Power BI. They are not a whole lot different than their counterparts in Excel. There are some distinct differences though and too many to discuss in this forum.
Everything done in the report so far is nice but does not address one concern.
What if the data does not display some calculation or another?
We have to add it ourselves.
Do we add it to the original spreadsheet before we connect the spreadsheet?
You can but it is not quite as efficient.
What if we do not think about this calculation until after we have already connected it?
That is what we are about to talk about.
Can a calculation be added to our spreadsheet after we have connected it to Power BI?
Yes, but it is awkward at best.
The best way to handle this situation is to do nothing with your original spreadsheet. Best practice is to keep your original data as it was originally sourced and just do a little coding.
Wait a minute, I did not bargain for that!
Don't worry. It is relatively easy, and I am going to tell you exactly what to do.
Let's say for instance that you want to know the difference Sales and Gross Sales.
In Power BI Online,
1) Access the report we have been working on.
2) Choose EDIT
3) Choose OPEN SEMANTIC MODEL (a button on the top row)
The view will change to a little table showing all of the table columns. There will be a green bar at the top that says, "You are in Viewing mode and changes will not be saved."
4) Switch to EDITING by selecting the green menu button in the very top right of the screen that currently says "Viewing"
BEWARE! Keep in mind your changes will be permanent and automatically saved.
5) Right-Click on the table name in the Data Pane on the right.
6) Select "New Measure"
A new box appears at the top of our space with red check box and a green check box which says "Measure = "
In the following steps, we are going to put in the following measure:
Sales Difference = SUM(financials[Gross Sales])-SUM(financials[ Sales])
Pay close attention to what is happening as you type.
7) In the Measure box type "Sales Difference =" over the top of "Measure ="
Nothing happens. Hrmm.
8) Type "SUM" then press <TAB>
Notice that as soon as you began to type, a drop-down of options appears. Those are other expressions that you can use. We are only going with SUM for right now.
Also notice that a "(" was added automatically for you when you hit <TAB>
9) Type "Gr ..."
The name of that column from the table appears suddenly
10) Press <ENTER>
11) Type ")" to close the argument.
Sorry. It does not know when to stop, so you have to tell it.
So far, we should look like "SUM(financials[Gross Sales])."
That is not enough to get us there.
12) Type "-"
Nothing happens.
13) Type "SUM" and press <TAB>
14) Type "Sales"
Now we have a problem. There are two to choose from. We already used one of them though, so that narrows things down.
15) Click the one titled "financials[ Sales]"
16) Type ")" to close the argument.
17) Press <ENTER>
If you did everything right, you should Power BI "Working on it ..." and then your Measure will appear in that box, without any errors or weird red lines underneath parts of it. If it has those things, erase it and start over.
If you cannot get it to work. Grab the full formula above and paste it in that box instead.
18) Switch back to "Viewing" mode by clicking that little button in the top right of your screen that currently says "Editing."
Notice that there now appears to be a column in the table for "Sales Difference." It is actually only a formula that will consistently calculate the difference between those two columns everywhere you use it. It will act like a column but really is not. It is only pretending.
What we have done is tell Power BI that every time we drop in this little pretend column, we want it to calculate the SUM of Gross Sales, calculate the SUM of Sales, then subtract the latter from the former. Easy, right?
You can now go back to your report and use this little formula anywhere you like; well, most anywhere. That is another story for another time though.
A couple of other related stories are New Columns and Visual Calculations.
When we first started with this, about the time of the right-click, you probably noticed you can also add "New Column." That is a whole different situation than what we just did, though it can work much the same, though on a row-by-row basis.
What is the difference?
Days of argument over which is more suitable for conditions in the report.
Stick with DAX for now.
Oh, I probably forgot to mention, that is what "code" we were writing.
While a lot of others try to define catalogs of the various expressions available for DAX, Microsoft has their own and is probably more reliable than others.
Data Analysis Expressions (DAX) Reference - DAX | Microsoft Learn
You might also discover or someone else may also tell you that you can simply use a "Visual Calculation."
You can. It is easier.
Understand that they only apply to the visual selected though and cannot be reused easily.
That is the best I can frighten you on this Halloween.
Happy Measuring!
No comments:
Post a Comment