Multiple field formatting values

[Anonymous]

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! 

[v-jingzhang · Accepted solution]

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.

[Anonymous]

Thank you so much for your help!!!!

[amitchandak]

@Anonymous , you need can create a slicer and use format based on that. You might end up getting text format data

how to use a slicer

https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

[Pragati11]

Hi @Anonymous ,

You will need to add details like screesnshots here for us to understand the issue clearly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Thanks,

Pragati