In this writing, I want to share how I use Tabular editor in Power BI to change format of a measure that I use in a visualization.
How to configure and write Format String Expression in Tabular Editor is useful to know especially when authors deal with currency.
I will try to share it in simple way, and I hope readers can step further to create complex version and apply it to a more complex situation.
I have a table like below.
Bar chart with custom label
I want to create a bar chart that shows quantity by customer. Additionally, I want to show label not only describing the quantity but also the ranking and percentage. The final result will be look like the below.
The first step is to create measures.
Qty total: = SUM( Data[Quantity] )
Ranking: = RANKX( ALL(Data[Customer]), [Qty total:],,DESC )
Percentage: = DIVIDE ( [Qty total:], CALCULATE ( [Qty total:], REMOVEFILTERS () ) )
The second step is to create a simple bar chart (quantity by customer) that shows quantity as a label, like the below picture.
The third step is to open Tabular editor and create "New Calculation Group" and "New Calculation Item"
Click the down arrow in the Property section (green), and select "Format String Expression".
Write the below DAX formula in the window.
When you see the above DAX formula, """" is there.
The reason is not 100% understandable, but some says that because I am showing number format together with text format as a label, I have to make everything as a text format. You can try not to add """" and check. You may see a weird number.
The last step is, save this calculation group, refresh the data model, and put the newly created column into the visual filter pane.
Table visualization with correct currency format
By using the table that is shown on the very top, I want to calculate total revenue like below.
First, write a measure and create a table visualization to show revenue by customer.
However, as you already saw in the picture from the top, customers are using different currency. Some are using USD currency, others are using EUR. In this case, total is wrong and it cannot be simply added up. I will explain how to fix the total in the later blog post.
In this table visualization, I can simply add currency column to show the correct currency, but my focus is to write Format String Expression in Tabular editor to show the correct currency.
The first step is to create new Calculation Group and Calculation Item like below.
The second step is, change the property view to Format String Expression and write DAX formula like below.
The last step is, save Calculation Group and Calculation Item, refresh the data model, and put the newly created column into the visual filter pane. And then each row shows a correct number with correct currency.
As I mentioned above, the total is not a correct number, because various currencies have to be converted into one currency before adding up. I will show this in a later blog post with adding currency table into the data model.
I hope this helps having fun in playing with LABEL in Power BI visualizations.
Comments