Forum Discussion

DmitryAD7's avatar
DmitryAD7
Icon for Helper I rankHelper I
2 years ago
Solved

Dynamically display currency for a measures based on slicer selection

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"
)

 

 

  • talespin's avatar
    talespin
    2 years ago

    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

7 Replies

  • talespin's avatar
    talespin
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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.

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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
    )
  • DmitryAD7's avatar
    DmitryAD7
    Icon for Helper I rankHelper I
    2 years ago

    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's avatar
      talespin
      Icon for Solution Sage rankSolution Sage
      2 years ago

      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
      • DmitryAD7's avatar
        DmitryAD7
        Icon for Helper I rankHelper I
        2 years ago

        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?