Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to rectify negative values on vizualization only

I have a column that can have both negative and positive values. When I filter this column and summarize it by average, the value may come out as zero/positive, or negative. I want to display the value only if it is zero or positive: If the summarization ended up being negative, I want to rectify the value and display 0 instead.

 

Does anyone know how to achieve this is power bi desktop?

 

Thanks in advance.

Need Help
Show and Tell
Tips and Tricks
  • selimovd's avatar
    selimovd
    5 years ago

    Hey Anonymous ,

     

    you can handle that in the measure. So let's assume the measure is like this:

     

    myAverage = AVERAGE ( myTable[myMeasure] )

     

     

    Then you can change it to that:

     

    myAverage =
VAR vAverage = AVERAGE( myTable[myMeasure] )
RETURN
    IF(
        vAverage > 0,
        vAverage,
        0
    )

     

     

    Would that work for you?

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

4 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    you can handle that in the measure. So let's assume the measure is like this:

     

    myAverage = AVERAGE ( myTable[myMeasure] )

     

     

    Then you can change it to that:

     

    myAverage =
VAR vAverage = AVERAGE( myTable[myMeasure] )
RETURN
    IF(
        vAverage > 0,
        vAverage,
        0
    )

     

     

    Would that work for you?

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks, this works indeed.

       

      Is it possible to apply this for a number of columns? For example, I want to rectify columns A, B and C. So I would have to create three measures:

      rectify_A =
VAR vAverage = AVERAGE( myTable[A] )
RETURN IF(vAverage > 0, vAverage, 0)

rectify_B =
VAR vAverage = AVERAGE( myTable[B] )
RETURN IF(vAverage > 0, vAverage, 0)

rectify_C =
VAR vAverage = AVERAGE( myTable[C] )
RETURN IF(vAverage > 0, vAverage, 0)

       which seems repetitive if there are many columns.

      • selimovd's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        5 years ago

        Hey Anonymous ,

         

        you can use Tabular Editor and the scripting task to automate that. Like select the columns you want to create a measure like mentioned above and then create them by script:

        https://docs.tabulareditor.com/Useful-script-snippets.html

         

        You could also do that with Calculation Groups, but you have the same work to do and it doesn't really make it easier.

         

        If you need any help please let me know.
        If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
         
        Best regards
        Denis
         
  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    5 years ago

    what is your measure calculation at the moment?

     

    quite simple to do just use an if statement

    ie 

    measure =  if(sum(table[measure]) < 0, 0, sum(table[measure]))