Dynamically display currency for a measures based on slicer selection

[DmitryAD7]

Hello,

Please help with dynamic format.

I need three conditions:

1. each country has its own currency (done)

2. if 2 specific countries are selected, display data in one currency (need help)

3. for all other cases - there is no formatting with currency, just numbers (done)

VAR _eu1 = FILTER ( 'Hierarchy', 'Hierarchy'[Country] = "Country 1" )
VAR _eu2 = FILTER ( 'Hierarchy', 'Hierarchy'[Country] = "Country 2" )
VAR _eu = _eu1 && _eu2
--
VAR _car1 = FILTER ( 'Hierarchy', 'Hierarchy'[Country] = "Country 3" )
VAR _car2 = FILTER ( 'Hierarchy', 'Hierarchy'[Country] = "Country 4" )
VAR _car = _car1 && _car2
--
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Hierarchy'[Country] ) = "Country 1", "A #,0.00",
        SELECTEDVALUE ( 'Hierarchy'[Country] ) = "Country 2", "B #,0.00",
        SELECTEDVALUE ( 'Hierarchy'[Country] ) = "Country 3", "C #,0.00",
        SELECTEDVALUE ( 'Hierarchy'[Country] ) = "Country 4", "D #,0.00",
        SELECTEDVALUE ( 'Hierarchy'[Country] ) = "Country 5", "E #,0.00",
		_eu1 && _eu2, "F #,0.00",
		_car1 && _car2, "G #,0.00",
		"#,0.00"
)

[DmitryAD7]

Hello @talespin and @Sahir_Maharaj 

Sample file. What I expect:

If all countries are selected in a slicer or none are selected, the default currency formatting is "#,#".
If one country is selected, the formatting for each country is different: “A #,#”, “B #,#”, “C #,#”, etc.
If only two countries are selected Country 1 and Country 2 - currency format is "F #,#".
If only two countries are selected Country 4 and Country 5 - currency format is "G #,#".

@Sahir_Maharajthanks, but In your solution, If all countries are selected in the slicer or none are selected, the default currency formatting is "F #,#", not "#,#".

[talespin · Accepted solution]

hi @DmitryAD7 

These two sound more like a hypothetical scenario. You don't want to hardcode currency/country/formattting. It should be dynamic. Please check below solution if it works as per your requirement.

If only two countries are selected Country 1 and Country 2 - currency format is "F #,#".
If only two countries are selected Country 4 and Country 5 - currency format is "G #,#".

Created duplicate table to yours and added currency symbol to table itself.

Then created this measure.

Value with Format =

VAR _Selected = ALLSELECTED(Hierarchy2[Country])

VAR _CountSelected = CALCULATE(COUNT(Hierarchy2[Country]), ALLSELECTED(Hierarchy2[Country]))

VAR _CountCountry = CALCULATE(COUNT(Hierarchy2[Country]), REMOVEFILTERS())

RETURN IF(

            _CountSelected = _CountCountry,

            FORMAT( SUM(Hierarchy2[Value]), "#,#"),

            IF(

                _CountSelected = 1,

                FORMAT(

                        SUM(Hierarchy2[Value]),

                        VAR _Currency = SELECTEDVALUE(Hierarchy2[Currency])

                        RETURN "\"&_Currency&" #,#"

                ),

                IF(

                    _CountSelected = 2 && "Country 1" IN ALLSELECTED(Hierarchy2[Country]) && "Country 2" IN ALLSELECTED(Hierarchy2[Country]),

                    FORMAT( SUM(Hierarchy2[Value]), "F #,#"),

                    IF(

                        _CountSelected = 2 && "Country 4" IN ALLSELECTED(Hierarchy2[Country]) && "Country 5" IN ALLSELECTED(Hierarchy2[Country]),

                        FORMAT( SUM(Hierarchy2[Value]), "G #,#"),

                        FORMAT( SUM(Hierarchy2[Value]), "#,#")

                    )

                )

            )

)

Pbix file

https://drive.google.com/file/d/1Io9Quo2uoI_kPnZWsYXJIcFjZk18ZULW/view?usp=sharing

[DmitryAD7]

Hello @talespin . No more help needed in that case, tahnks a lot for your help. I have converted your measure to a dynamic format string.

Thanks!

[talespin]

hi @DmitryAD7 

You're welcome.

[DmitryAD7]

Good day @talespin .

Thank you, the result is correct now).

But I don't want to add this condition to my main measure, (not to complicate it). I would like to use the dynamic data format option:

could you please help to transform you measure for this option?

[Sahir_Maharaj]

Hello @DmitryAD7,

Can you please try the following:

Dynamic Currency Format = 
VAR _selectedCountries = ALLSELECTED('Hierarchy'[Country])
VAR _isEuSelected = 
    CONTAINS(_selectedCountries, 'Hierarchy'[Country], "Country 1") &&
    CONTAINS(_selectedCountries, 'Hierarchy'[Country], "Country 2")
VAR _isCarSelected = 
    CONTAINS(_selectedCountries, 'Hierarchy'[Country], "Country 3") &&
    CONTAINS(_selectedCountries, 'Hierarchy'[Country], "Country 4")
VAR _selectedCount = COUNTROWS(_selectedCountries)

RETURN
    SWITCH (
        TRUE(),
        _selectedCount = 1 && SELECTEDVALUE('Hierarchy'[Country]) = "Country 1", "A #,0.00",
        _selectedCount = 1 && SELECTEDVALUE('Hierarchy'[Country]) = "Country 2", "B #,0.00",
        _selectedCount = 1 && SELECTEDVALUE('Hierarchy'[Country]) = "Country 3", "C #,0.00",
        _selectedCount = 1 && SELECTEDVALUE('Hierarchy'[Country]) = "Country 4", "D #,0.00",
        _selectedCount = 1 && SELECTEDVALUE('Hierarchy'[Country]) = "Country 5", "E #,0.00",
        _isEuSelected, "F #,0.00",  // For combined selection of Country 1 and Country 2
        _isCarSelected, "G #,0.00", // For combined selection of Country 3 and Country 4
        "#,0.00"  // Default formatting
    )

[talespin]

hi @DmitryAD7 

I believe you are writing too many lines of code to format. If you can explain what you have and what you expect, maybe we can propose a better solution.

- Sample data from table.

- What output you expect.