SUM the negative value for each week in a different table

[raphael_cabral]

Hi there,

I need help!  I am new in PowerBi world so I am not sure how I can use DAX to complete a task that takes me hours every week in excel.. here we go.

I have a file that shows the forecast for 100s of products spread by week, as you can see below.

Is it possible to create a new table with the same week columns, but showing only the negative value and SUM it the by commodity? 

I only care about the negative values from the spreadsheet so I am not sure what will be the best solution for my problem... I am open for suggestions.  

thank you very much.

Raphael

[v-zhenbw-msft · Accepted solution]

Hi @raphael_cabral ,

We can use the unpivot function and create a measure to meet your requirement.

1. In Power Query Editor, select the date columns, and unpivot them.

2. Then create a measure to calculate the negative value.

Measure = 
var value_ = CALCULATE(SUM('Table (2)'[Value]))
return
IF(value_<0,value_,BLANK())

And create a matrix table like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[raphael_cabral]

Thank you very much .... we are almost there.. 🙂

I did what you suggested and it only works if I leave the product as the first in the row...if I switch to commodity I don't get any result.  do you know why? I don't need to know the product .. it is a report for the management so just the commodity is fine.

With PartJust Commodity

[sanimesa]

@raphael_cabral  A possibly easy workaround: if the value in the table comes from a column, then you can simply create a Matrix with the commodities on the row, the dates as you have on the column and put the same value in with summarization (which will sum it for each cell). Then you can add a filter to this table where the column value is < 0.

[raphael_cabral]

hi Sanimesa, the problem with this solution is when I create the matrix as you suggested, and I filter the first column with the week 01  as "is less than" 0, it filters all matrix not only the week 01.

[amitchandak]

@raphael_cabral Assuming you have a  measure  = [measure]

then this measure

sumx(filter(summarize(table, table[commodity], table[product], table[Week], "_mes",[measure]),[_mes]<0),[_mes])