Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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! 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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

 

Sample data ‘Sales’

v-jingzhang_0-1599206947986.png

 

  • 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 ()
)

 

v-jingzhang_2-1599206947990.png

 

  • 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.

2.jpg

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.

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

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

 

Sample data ‘Sales’

v-jingzhang_0-1599206947986.png

 

  • 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 ()
)

 

v-jingzhang_2-1599206947990.png

 

  • 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.

2.jpg

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
Not applicable

Thank you so much for your help!!!!

amitchandak
Super User
Super User
Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.