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

Replacing $0 values with a dash in a matrix

Hello,

 

I'm trying to turn all $0 values in my matrix table to appear like a - similar to how the Accounting format works in Excel. For example, how would I change the underlined value below appear as a - instead of $0?

 

Example.png

 

1 ACCEPTED SOLUTION

Hi @Anonymous,

You can modify it according to the below formula:

Measure = IF(CALCULATE(SUM(Table1[Order number]),FILTER('Table1','Table1'[Month]=MAX('Table1'[Month])))=1,"-",CALCULATE(SUM(Table1[Order number])))

Result:

1.PNG

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
joe_darling
Frequent Visitor

If anyone is struggling with this, you can you check out Power BI's native custom format strings which allow you to format Positive, Negative, and Zeros in a similar way you can in Excel:

 

Use custom format strings in Power BI Desktop 

 

joe_darling_0-1681483639543.png

 

These custom format strings apply to all data types, not just numbers.

 

When you choose to change to a custom format, Power BI will generate a positive and negative custom format string for you. You likely only need to add ;"-" to the end of that string and you'll be all set.

v-danhe-msft
Employee
Employee

Hi @Anonymous,

Due to I could not reproduce your data structure, you could refer to below steps:

Sample data:

1.PNG

Create a measure(here I replaced the 1 to "-"):

Measure = IF(CALCULATE(SUM(Table1[Order number]),FILTER('Table1','Table1'[Month]=MAX('Table1'[Month])))=1,"-",CALCULATE(SUM(Table1[Order number]),FILTER('Table1','Table1'[Month]=MAX('Table1'[Month]))))

Result:

1.PNG

If I misunderstand you, could you please offer me your sample file if possible? Also, you could download my pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks @v-danhe-msft, that formula actually almost worked! However, it seems like the totals aren't adding up at the bottom as a Grand Total. You can see this in your example as well (the total at the bottom should show up as 13 in your example, but is 3). Any way to fix the vertical grand total on the table as a sum?

Hi @Anonymous,

You can modify it according to the below formula:

Measure = IF(CALCULATE(SUM(Table1[Order number]),FILTER('Table1','Table1'[Month]=MAX('Table1'[Month])))=1,"-",CALCULATE(SUM(Table1[Order number])))

Result:

1.PNG

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi

 

Use this formula:

 

New column = IF ( Table1[Ext_column]=0,"-",FORMAT(Table1[Ext_column],0))

 

Thanks

Raj

Anonymous
Not applicable

The numbers shown above are sum aggregated. Once I create the column it seems there is no way to sum it and any attempt to turn it into a numeric column give me an error. Ay way to make this work while still being able to sum up the values?

Anonymous
Not applicable

Can you use this on top of your final sum or calculation? I used column as an example.

 

Thanks
Raj

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.