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 even if a change yearmonth

Negativevalues = CALCULATE(SUMX(FILTER(FAIT_INDICATEURS, FAIT_INDICATEURS[Disponibilité Restante]<0), FAIT_INDICATEURS[Disponibilité Restante]), FAIT_INDICATEURS[MOIS] = FORMAT(EDATE((TODAY()), 0), "YYYYmm"))
 
The card Disponibilite restante works well flitering the yearmonth(FAIT_INDICATEURS[MOIS]
 

We have 4 levels rows for this table: REF_HIERARQUIE_OBS_RESSOUCE[NIV4], REF_HIERARQUIE_OBS_RESSOUCE[NIV5],
REF_RESSOURCE[Nom de la ressource], REF_PROJET[Nom du projet].

 

I need to calculte the negtive values from example 202502 the value of -25

 

tks all in advance!!

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. 

5 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    1 year ago

    Hi,

     

    Try to wrap the dax in sumx and see if it works. else, share sample data for the solution.

     

    Negative value = SUMX ( FILTER ( FAIT_INDICATEURS, FAIT_INDICATEURS[Remaining Availability] < 0 && FAIT_INDICATEURS[MONTH] = FORMAT ( EDATE (( TODAY ()), 0 ), "YYYYmm" ), FAIT_INDICATEURS[Remaining Availability])

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

      SUMX ( FILTER ( FAIT_INDICATEURS, FAIT_INDICATEURS[Remaining Availability] < 0 && FAIT_INDICATEURS[MONTH] = FORMAT ( EDATE (( TODAY ()), 0 ), "YYYYmm" ), FAIT_INDICATEURS[Remaining Availability]) 

       

      We have a issue to add the last part if the measure

  • 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.