Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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?

 

 

  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi

     

    Use this formula:

     

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

     

    Thanks

    Raj

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

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

         

        Thanks
        Raj

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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?

      • v-danhe-msft's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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

  • joe_darling's avatar
    joe_darling
    Frequent Visitor
    3 years ago

    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.