Forum Discussion

Rohini's avatar
Rohini
Helper I
4 years ago
Solved

Custom Format each row based on column

I have data in the below format with the columns 'Metric', 'Actual Value' and 'Metric Format'. Based on the column 'Metric Format', I would like to populate the column 'Desired Outcome' in the visual (matrix) in the output.

Input:

MetricActual ValueMetric Format
A0.880%
B2497#,##0
C435$#,##0

 

Output:

MetricDesired Outcome
A88%
B2,497
C$435

An ex-employee was able to do this and I am trying to figure out where the settings need to change. Would appreciate if anyone can help.

 

Need Help
  • Rohini's avatar
    Rohini
    4 years ago

    The below code worked:

     

    Desired Outcome = if( Not(ISERROR(value('TableName'[Actual Value]))), format(value('TableName'[Actual Value]),'TableName'[Metric Format]),'TableName'[Actual Value])

2 Replies

  • Rohini's avatar
    Rohini
    Helper I
    4 years ago

    The below code worked:

     

    Desired Outcome = if( Not(ISERROR(value('TableName'[Actual Value]))), format(value('TableName'[Actual Value]),'TableName'[Metric Format]),'TableName'[Actual Value])

  • goncalogeraldes's avatar
    goncalogeraldes
    Super User
    4 years ago

    Hello there Rohini ! Check if this works for you:

    Desired Outcome =
VAR _current_row =
    SELECTEDVALUE ( 'Your Table'[Actual Value] )
RETURN
    SWITCH (
        _current_row,
        "0%", FORMAT ( _current_row, "Percent" ),
        "#,##0", FORMAT ( _current_row, "#,0" ),
        "#,##0", FORMAT ( _current_row, "$#,0" )
    )

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes