In this writing, I want to share how I display currency together with a sum amount in a matrix visualization, in Power BI.
By utilizing Tabular Editor, I can show the total amount dynamically by selecting the currency I want.
The data that I used in this writing is randomized for a demonstration purpose, and the structure of a matrix table that is shown here is generally used in the freight forwarding and transportation industry to analyze transportation costs.
Simplifying the price structure that is practically used in the freight forwarding industry or in the transportation industry in order to briefly describe the table, it could be divided into three parts.
Charges at the origin side,
freight charges between the origin and the destination,
and charges at the destination side.
If the scenario is to transport the cargo from A to B in the same country, currency does not matter.
However, if the scenario is to transport from The Netherlands to Singapore, then currency starts to create a bit of confusion in understanding the total costs, if it is not properly shown. In this case, for instance, origin charges are shown in EUR currency, freight charges are shown in USD currency (or in EUR currency), and destination charges are shown in SGD currency. Furthermore, if your organization has over 10 branches in 10 different countries, or your customer requests the price from 10 countries to all over the world, the number starts to create a bit of headache if end users want to see the currency.
Because not only each column but also each row shows different currency.
Previously I had a similar situation and I needed to let the end-users select the currency they want. By writing a DAX measure like below, I could make it happen and I could let end-users select the currency in a slicer.
Total charge =
(
[Origin charge]
* DIVIDE (
SELECTEDVALUE ( ccy[UnitsperEUR] ),
LOOKUPVALUE ( ccy[UnitsperEUR], ccy[ccy], VALUES ( data[Orig ccy] ) )
)
)
+
(
[Freight charge]
* DIVIDE (
SELECTEDVALUE ( ccy[UnitsperEUR] ),
LOOKUPVALUE ( ccy[UnitsperEUR], ccy[ccy], VALUES ( data[Freight ccy] ) )
)
)
+
(
[Dest charge]
* DIVIDE (
SELECTEDVALUE ( ccy[UnitsperEUR] ),
LOOKUPVALUE ( ccy[UnitsperEUR], ccy[ccy], VALUES ( data[Dest ccy] ) )
)
)
But, I could not come up with a nicer way to display the table by mentioning each currency of each number.
I wanted to show the information shown below to end-users.
It clearly shows that what is the currency of origin charge, freight charge, destination charge, converted amount of each & total charge in a selected currency.
Even if several different currencies are shown in the same column, it is doable if the correct measure is written in the Format String Expression area in Tabular Editor.
In order to show each row’s & each column’s currency symbol correctly, I wrote the below measure in Tabular Editor.
IF (
ISSELECTEDMEASURE ( [Origin charge] ),
CALCULATE (
SELECTEDVALUE ( 'ccy'[ccy_symbol_format] ),
TREATAS ( VALUES ( 'data'[Orig ccy] ), 'ccy'[ccy] )
),
IF (
ISSELECTEDMEASURE ( [Dest charge] ),
CALCULATE (
SELECTEDVALUE ( 'ccy'[ccy_symbol_format] ),
TREATAS ( VALUES ( 'data'[Dest ccy] ), 'ccy'[ccy] )
),
IF (
ISSELECTEDMEASURE ( [Freight charge] ),
CALCULATE (
SELECTEDVALUE ( 'ccy'[ccy_symbol_format] ),
TREATAS ( VALUES ( 'data'[Freight ccy] ), 'ccy'[ccy] )
),
IF (
ISSELECTEDMEASURE ( [Total charge] ) || ISSELECTEDMEASURE ( [O.C.Convert] )
|| ISSELECTEDMEASURE ( [F.C.Convert] )
|| ISSELECTEDMEASURE ( [D.C.Convert] ),
SELECTEDVALUE ( 'ccy'[ccy_symbol_format] ),
"#,##0"
)
)
)
)
I hope this helps freight forwarders and transportations companies to make their customers seeing the price table with satisfaction by showing all the currency symbols and all being correctly converted to the preferred currency.
I publish to web to demonstrate like below.
I attach a sample pbix file to this blog post.
Comments