Forum Discussion

Balaji1414's avatar
Balaji1414
Frequent Visitor
5 years ago
Solved

Different data types in a single column

Hi All,

In my report i have a table and the data types are different (have $,%,text, numbers in a single column) in columns, i need to add a backround color for the data, any leads please

Many thanks in advance!

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Balaji1414 ，

    Sorry for delay. I updated my sample pbix file(see attachment), please check whether that is what you want.

    Conditional formatting =
    VAR _curcolumn =
    SELECTEDVALUE ( 'Table'[Q1] )
    VAR _includesc =
    IFERROR ( SEARCH ( "$", _curcolumn, 1, 0 ), 0 )
    VAR _scnumber =
    IFERROR ( VALUE ( RIGHT ( _curcolumn, LEN ( _curcolumn ) - 1 ) ), 0 )
    VAR _includepc =
    IFERROR ( SEARCH ( "%", _curcolumn, 1, 0 ), 0 )
    VAR _pcnumber =
    IFERROR ( VALUE ( LEFT ( _curcolumn, LEN ( _curcolumn ) - 1 ) ) / 100, 0 )
    VAR _number =
    IFERROR ( VALUE ( _curcolumn ), 0 )
    RETURN
    SWITCH (
    TRUE,
    _includesc > 0,
    IF ( _scnumber > 500, "#315496", IF ( _scnumber < 500, "#f6af82" ) ),
    _includepc > 0, IF ( _pcnumber > 0.5, "#a9d092", "#ffff01" ),
    IF ( _number > 75, "#00af50", IF ( _number < 50, "#fe0000" ) )
    )

    Best Regards

6 Replies

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Balaji1414 ，

        Sorry for delay. I updated my sample pbix file(see attachment), please check whether that is what you want.

        Conditional formatting =
        VAR _curcolumn =
        SELECTEDVALUE ( 'Table'[Q1] )
        VAR _includesc =
        IFERROR ( SEARCH ( "$", _curcolumn, 1, 0 ), 0 )
        VAR _scnumber =
        IFERROR ( VALUE ( RIGHT ( _curcolumn, LEN ( _curcolumn ) - 1 ) ), 0 )
        VAR _includepc =
        IFERROR ( SEARCH ( "%", _curcolumn, 1, 0 ), 0 )
        VAR _pcnumber =
        IFERROR ( VALUE ( LEFT ( _curcolumn, LEN ( _curcolumn ) - 1 ) ) / 100, 0 )
        VAR _number =
        IFERROR ( VALUE ( _curcolumn ), 0 )
        RETURN
        SWITCH (
        TRUE,
        _includesc > 0,
        IF ( _scnumber > 500, "#315496", IF ( _scnumber < 500, "#f6af82" ) ),
        _includepc > 0, IF ( _pcnumber > 0.5, "#a9d092", "#ffff01" ),
        IF ( _number > 75, "#00af50", IF ( _number < 50, "#fe0000" ) )
        )

        Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Balaji1414 ,

    I created a sample pbix file(see attachment), please check whether that is what you want.

    1. Create a measure as below to set the color for different type of data(include $,%,text, number etc.)

    Note: You can change the color inside the formula to the color you want.

    Conditional formatting = 
VAR _curcolumn =
    SELECTEDVALUE ( 'Table'[Column] )
VAR _includesc =
    IFERROR ( SEARCH ( "$", _curcolumn, 1, 0 ), 0 )
VAR _includepc =
    IFERROR ( SEARCH ( "%", _curcolumn, 1, 0 ), 0 )
VAR _number =
    IFERROR ( VALUE ( _curcolumn ), 0 )
RETURN
    IF (
        _includesc > 0,
        "red",
        IF ( _includepc, "orange", IF ( _number > 0, "purple", "green" ) )
    )

    2. Make conditional formatting for background color of the column(Format by Field value)

    Color based on a calculation

    Best Regards