Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Error on Subtracting two measures having format

Hi PaulDBrown  and @all, Here is one more Issue i am facing. I think you can fix this.  Continuing to the link below. https://community.powerbi.com/t5/Desktop/sign-currency-for-only-few-rows-bas...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can try to use the following measure to meet your requirement:

     

    Change =
VALUE (
    SUBSTITUTE (
        [January Total] & "",
        "$",
        ""
    )
)
    - VALUE (
        SUBSTITUTE (
            [February Total] & "",
            "$",
            ""
        )
    )

     

    If it does not work, could you please share the formula of [Janury Total] and [February Total] if it does not contain any confidential information?


    Best regards,

     

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous 

 

Make sure that all columns and measures used in the calculations are formatted as some sort of number (not "general" nor "text" etc..)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks for reply PaulDBrown 

    The calculation used for Change are measures [January Total] and [February Total] which have the same data type as shown in below screenshot.

     Change = [January Total] - [February Total] 

    Still results in same error.  Is there any thing else I am missing ?

    • santiagomur's avatar
      santiagomur
      Icon for Resolver II rankResolver II
      6 years ago

      i think some of the columns you use to calculate january total or february total is txt or some are date and other numeric. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi santiagomur ,

        Thanks for reply. I have checked with calculating columns and measures. All of them are set to Decimal Number.

    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous 

      Try changing the "Data Type: variant" to decimal number.

       

      If you can't do it in the measure formatting options, make sure your columns you are refrencing in the measures for the calculations are formatted as decimal numbers.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi PaulDBrown ,

        Unable to change the data type from Variant as few are having $ format and few don't. I have checked from lowest calculating columns and measures. I have changed them from General to Decimal Number still its the same error.