Mesure to filter negative values

[fbittencourt]

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!!

[Anonymous · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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. 

[Rupak_bi]

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]

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