Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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:
Regards,
Daniel He
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
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.
Hi @Anonymous,
Due to I could not reproduce your data structure, you could refer to below steps:
Sample data:
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:
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
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:
Regards,
Daniel He
Hi
Use this formula:
New column = IF ( Table1[Ext_column]=0,"-",FORMAT(Table1[Ext_column],0))
Thanks
Raj
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?
Can you use this on top of your final sum or calculation? I used column as an example.
Thanks
Raj
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |