Forum Discussion

fbittencourt's avatar
fbittencourt
Icon for Helper IV rankHelper IV
1 year ago
Solved

Mesure to filter negative values

It´s supose to be simple but it not working my mesure, I need to calculate the negative values from the yearmonth column as a mesure to show in a card.   This mesure retrieves a fixed  value = - 20...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi fbittencourt ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data.  

     

    (2) We can create a calculated column.

    Column = FORMAT([MOIS],"yyyymm")

    (3) We can create a measure.

    Measure = 
var _a=CALCULATE(SUM('Table'[Disponibilité Restante]),FILTER(ALL('Table'),[Column]=FORMAT(EDATE(TODAY(),0),"yyyymm") &&'Table'[Nom du projet]=MAX('Table'[Nom du projet]) && 'Table'[Nom de la ressource]=MAX('Table'[Nom de la ressource]) && 'Table'[NIV4]=MAX('Table'[NIV4]) && [Disponibilité Restante]<0))
var _b=CALCULATE(SUM('Table'[Disponibilité Restante]),FILTER(ALL('Table'),[Column]=FORMAT(EDATE(TODAY(),0),"yyyymm") &&'Table'[Nom du projet]=MAX('Table'[Nom du projet]) && 'Table'[Nom de la ressource]=MAX('Table'[Nom de la ressource]) && 'Table'[NIV4]=MAX('Table'[NIV4]) ))
RETURN _a+_b
    Measure 2 = 
var _a=FORMAT(TODAY(),"yyyymm") 
RETURN IF([Measure]<0 && MAX('Table'[Column]) =_a ,[Measure],BLANK())
    Measure 3 = MAXX( ADDCOLUMNS('Table',"value",[Measure 2]),[value])

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi fbittencourt ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2) We can create two calculated columns.

    negative values = CALCULATE(SUM('Table'[Disponibilité Restante]),FILTER('Table',[Disponibilité Restante]<0) )
    positive values = CALCULATE(SUM('Table'[Disponibilité Restante]),FILTER('Table',[Disponibilité Restante]>=0) )

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

    Refer to:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi fbittencourt ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

 

(2) We can create a calculated column.

Column = FORMAT([MOIS],"yyyymm")

(3) We can create a measure.

Measure = 
var _a=CALCULATE(SUM('Table'[Disponibilité Restante]),FILTER(ALL('Table'),[Column]=FORMAT(EDATE(TODAY(),0),"yyyymm") &&'Table'[Nom du projet]=MAX('Table'[Nom du projet]) && 'Table'[Nom de la ressource]=MAX('Table'[Nom de la ressource]) && 'Table'[NIV4]=MAX('Table'[NIV4]) && [Disponibilité Restante]<0))
var _b=CALCULATE(SUM('Table'[Disponibilité Restante]),FILTER(ALL('Table'),[Column]=FORMAT(EDATE(TODAY(),0),"yyyymm") &&'Table'[Nom du projet]=MAX('Table'[Nom du projet]) && 'Table'[Nom de la ressource]=MAX('Table'[Nom de la ressource]) && 'Table'[NIV4]=MAX('Table'[NIV4]) ))
RETURN _a+_b
Measure 2 = 
var _a=FORMAT(TODAY(),"yyyymm") 
RETURN IF([Measure]<0 && MAX('Table'[Column]) =_a ,[Measure],BLANK())
Measure 3 = MAXX( ADDCOLUMNS('Table',"value",[Measure 2]),[value])

(3) Then the result is as follows.

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

  • fbittencourt's avatar
    fbittencourt
    Icon for Helper IV rankHelper IV
    1 year ago

    Thanks for your time:

     

    What I need is more simple let me show in a simple way:

    I need one calculated column or measure to retrive only the negative values = -8 and other column just with positive values  = 82

     

    Disponibilité Restante = FAIT_INDICATEURS[DISPO] - FAIT_INDICATEURS[Charge] * calculated column
    Dispo restante = sum(FAIT_INDICATEURS[DISPO])-sum(FAIT_INDICATEURS[Charge]) * mesure
     
    I need to add to my mesure that is not working well the filter Id Ressource to retrieve negative values ( First mesure)
    and other mesure only positive values ( second mesure)

    SURPLAN 7 = CALCULATE(SUMX(FILTER(FAIT_INDICATEURS, FAIT_INDICATEURS[Disponibilité Restante]<0), FAIT_INDICATEURS[Disponibilité Restante]))
     
    Sorry to bother you again, many tks !!!





    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi fbittencourt ,

       

      According to your description, here are my steps you can follow as a solution.

      (1) This is my test data. 

      (2) We can create two calculated columns.

      negative values = CALCULATE(SUM('Table'[Disponibilité Restante]),FILTER('Table',[Disponibilité Restante]<0) )
      positive values = CALCULATE(SUM('Table'[Disponibilité Restante]),FILTER('Table',[Disponibilité Restante]>=0) )

      (3) Then the result is as follows.

       

      If the above one can't help you get the desired result, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

      Refer to:

      How to provide sample data in the Power BI Forum

      How to Get Your Question Answered Quickly

       

      Best Regards,

      Neeko Tang

      If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.