Display on the rows how have a negative value

[Applicable88]

Hello,

I'm using a matrix table were I calculate the total of stock material minus the range sum of the remaining count of stockmaterial. It's a kind of forecast to make sure enough stockmaterial is available. The dimension are the dates.

For simplicity reasons I only write the code I use for that:

RangeSum = [SumOfTotalAmount] -calculate(sum(Table1[Stockmaterial]), filter(allselected(Table1), Table1[Date].[Date] <= max(Tabl1[Date])))

The table is fine so far. I also add a color formatting, when values of rangesum is greater than 0 than its green otherwise red. 

Now I have a table where I see all material and all their values of that specific day. But I also want to provide a matrix view where you hide all the positvie ones and quickly see the "problem cases",  Stockmaterials which have negative amount. I tried to use that:

Measure=
var _RangeSumVar = [SumOfTotalAmount] -calculate(sum(Table1[Amount]), filter(allselected(Table1), Table1[Date].[Date] <= max(Tabl1[Date])))

return

if ( _RangeSumVar<0 ,  _RangeSumVar, Blank())

I filtered out correctly the stockmaterials with negative values, but the problem now what I have is that it only displays the negative days. But I also want to see the the other days of that Stockmaterial where its positive

[v-deddai1-msft · Accepted solution]

Hi @Applicable88 ,

Please try the following measure:

measure1 =
VAR a =
    SUMMARIZE (
        ALL ( Table1 ),
        Table1[stockmaterial],
        Table1[Date],
        "RangeSum",
            [SumOfTotalAmount]
                - CALCULATE (
                    SUM ( Table1[Stockmaterial] ),
                    FILTER ( ALLSELECTED ( Table1 ), Table1[Date] <= MAX ( Table1[Date] ) )
                )
    )
RETURN
    IF (
        MINX (
            FILTER ( a, Table1[stockmaterial] = MAX ( Table1[stockmaterial] ) ),
            [RangeSum]
        ) < 0,
        SUMX (
            FILTER (
                a,
                Table1[stockmaterial] = MAX ( Table1[stockmaterial] )
                    && Table1[Date] = MAX ( Table1[Date] )
            ),
            [RangeSum]
        )
    )

If it doesn't work, please show us sample pbix file. Then we can help you more accurately.

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

Best Regards,

Dedmon Dai

[Applicable88]

Hello @mwegener , 

thats not the same for my example.

I mad a sample visual in Excel, which is the same what I have in my original data in the PowerBi Matrix Visual. Here you can see the first table consisting everything. What I want is the view number 2, but instead I get number 3 with my function:

So I want to filter the stockmaterials which have a negative rangesum values somwhere in the dates, but also displaying the positive ones, to see how long stock is enough until its minus. 

I hope it was clearer. 

Best reagards. 

[mwegener]

Hi @Applicable88 

try this.

Create a measure that calculates the Min value (over each day) over the date table. Use this measure as a filter on the visual and filter the measure to "is less than 0".

MIN Stock = MINX(Dates,[RangeSum])

[Applicable88]

@mwegener , unfortunately thats also not working. Instead of display things differently it has also a impact on the range sum calculation, which make it returns wrong values. 

I also adapt to the fact that I have no mastercalendar and refer to the date column of that table itself. 

Still any ideas how I can get to view 2 like in the screenshot?

Best regards. 

[mwegener]

Hi @Applicable88 ,

can you provide a sample file?

[mwegener]

Hi @Applicable88 ,

is this something that the "Show items with no data" setting can fix?

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data