Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiple field formatting values

Hi, I have values on a matrix which I want to view in millions (i.e. $2M) and also values which I don't want to display units for as they are too small (i.e. $2).    These values aren't shown toge...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous , not sure how your data is organized, but you may take below steps for reference.

     

    Sample data ‘Sales’

     

    • If the large numbers and small numbers are in different fields:

    1. Create a table ‘Years’ and enter the values 2013 and 2014.

    2. Pick a slicer and add the Year column to its field.

    3. Create a measure to display value in the matrix. You can modify the format here.

     

    Display Sales =
IF (
    ISFILTERED ( Years[Year] ),
    SWITCH (
        SELECTEDVALUE ( Years[Year] ),
        2013, SUM ( Sales[Sales 2013] ),
        2014, SUM ( Sales[Sales 2014] ) / 1000000 & "M"
    ),
    BLANK ()
)

     

     

    • If the large numbers and small numbers are in the same field:

    1. Create a measure to display the value you need, you can modify the format here as you like.

     

    Measure 2014 =
VAR sumValue = SUM ( Sales[Sales 2014] )
RETURN
    IF ( sumValue > 1000000, sumValue / 1000000 & "M", sumValue )

     

    2. Add the measure in the matrix.

    Best Regards,

    Community Support Team _ Jing Zhang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.