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 together because of a slicer I have in place - it will either show me very large values ($M) or smaller values. 

 

I want my field formatting to change automatically as I use my slicer to either display units in millions if the numbers are large, and conversly, not have any units displayed if they are small.  Is this possible?

 Thanks in advance! 

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.

4 Replies