Forum Discussion
GBP & Eur help
Hello all,
I have some cost data stored in a database as numeral values. Alongside it I have categorys whether it is GPB or EURO - i want these categories to determine how the data is viewed on the graph, i.e. if a filter is selected for GPB, the data will show all relevant rows and show the graph as £ and if Euro is selected it will then filter for relevant rows and show data as Euro?
Any guidance?
James
Ensure your cost data table has a numeric column for costs and a column that indicates the currency category (e.g., "CurrencyType" with values like "GBP" or "EURO").
Go to the Fields pane in Power BI and drag the CurrencyType column to the report canvas to create a slicer. This allows users to select either "GBP" or "EURO."
Create a Measure
DynamicCost =
VAR SelectedCurrency = SELECTEDVALUE('YourTable'[CurrencyType])
VAR TotalCost = SUM('YourTable'[Cost])
RETURN
SWITCH(
SelectedCurrency,
"GBP", FORMAT(TotalCost, "Currency"),
"EURO", FORMAT(TotalCost, "€#,0.00"),
TotalCost
)Use a suitable visual (like a bar chart or line chart) and place the DynamicCost measure in the values field.
Add any necessary dimensions (like categories, dates, etc.) to the axis of the graph.Now, when you select "GBP" or "EURO" from the slicer, the visual should dynamically update to show the costs formatted according to the selected currency.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
5 Replies
- rajendraongole1
Super User
Hi jamesfbeard - Ensure that your data includes a column for currency categories (e.g., "GBP", "EURO") and that your cost values are stored in the same table or a related table.
In Power BI, use the Currency Category column (e.g., "Currency") as a Slicer on your report page. This will allow users to select either GBP or Euro to filter the data accordingly.
ref link:
Dynamic Currency Conversion with DAX in Power BI - Ben's Blog (datakuity.com)
Format Currencies In Power BI Using DAX – Master Data Skills + AI (enterprisedna.co)
Create a new DAX column with formatted currency type:
FormattedCurrency = SWITCH( TRUE(), TableName[Territory] = "Euro", FORMAT(TableName[Amount], "€#,##0.00"), TableName[Territory] = "GBP", FORMAT(TableName[Amount], "£#,##0.00"), TableName[Territory] = "USD", FORMAT(TableName[Amount], "$#,##0.00"), FORMAT(TableName[Amount], "#,##0.00") // Default format )Of course, replace column and table names accordingly.
Hope it helps!- Kedar_Pande
Ensure your cost data table has a numeric column for costs and a column that indicates the currency category (e.g., "CurrencyType" with values like "GBP" or "EURO").
Go to the Fields pane in Power BI and drag the CurrencyType column to the report canvas to create a slicer. This allows users to select either "GBP" or "EURO."
Create a Measure
DynamicCost =
VAR SelectedCurrency = SELECTEDVALUE('YourTable'[CurrencyType])
VAR TotalCost = SUM('YourTable'[Cost])
RETURN
SWITCH(
SelectedCurrency,
"GBP", FORMAT(TotalCost, "Currency"),
"EURO", FORMAT(TotalCost, "€#,0.00"),
TotalCost
)Use a suitable visual (like a bar chart or line chart) and place the DynamicCost measure in the values field.
Add any necessary dimensions (like categories, dates, etc.) to the axis of the graph.Now, when you select "GBP" or "EURO" from the slicer, the visual should dynamically update to show the costs formatted according to the selected currency.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInKedar This has worked perfectly and I can see the values are now changing - is there anyway to get it to show the £ or € symbol in the Y axis depending on the slicer selection - or get the title of the graph to dynamically change depending on slicer selection, e.g. "Total Invoice Cost - GBP" when GBP selected or "Total Invoice Cost - EUR" when EUR is selected?
