MIN of Calculated Column using measure as FILTER (Inventory Report)

[thmonte]

I can't wrap my head around this I feel like I am close but I am unable to get what I need

I have a table that I have filtered down to a perfect state.  I am trying to create a new column in this table with a measure that will tell the end user what line item they will get up to in their inventory.

The Rolling Boxes Given column is a measure that will take the Remaining Boxes On Site number and subtract it from a starting number and continue subtracting for each row.

Once I get to negative that means the user will not have to use any inventory in that row.

My idea was to use MAX on the calculated column and filter all the negative numbers using the "Rolling Boxes Given" as a filter to identify the negatives.

Once I have identified the MAX negative which in my picture should be 193 I can then write a switch statement to say

Measure = BLANK, 0,

Measure = Column, Rolling Boxes Given,

"Not Distributed"

[v-zhenbw-msft · Accepted solution]

Hi @thmonte ,

Could we ask how your Rolling Boxes Given Measure is written?

Is it convenient for you to convert it to Calculate Column?

We create a sample, if you can create a calculate column to replace it, then maybe our measure can help you.

Measure = 
VAR _min =
    CALCULATE (
        MIN ( 'Table'[Column] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Rolling Boxes Given] < 0 )
    )
RETURN
    IF ( SUM ( 'Table'[Rolling Boxes Given] ) >= 0, BLANK (), _min )

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data and put your measure in it?

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

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.

BTW, pbix as attached.

[thmonte]

@v-zhenbw-msft This worked out great.  I didnt think to make it a calculated column but I made some adjustments and I now have the measure working.

One problem....The Total at the bottom of my table is not showing correctly even tho the individual rows are and I don't know how to fix it.

I've tried this article that @Greg_Deckler posted but I am a little lossed because I have a switch statement in my measure so its not so black and white.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Column = my table index

Calculated Column = The new column I created that has the rolling inventory used

Est. EOD = 

VAR _min =
    CALCULATE (
        MIN ( shipment[Column] ),
        FILTER ( ALLSELECTED ( shipment ), shipment[Calculated Column] < 0 )
    )
VAR __min = IF ( SUM ( shipment[Calculated Column] ) >= 0, BLANK (), _min )    
VAR _index = CALCULATE(SUM(shipment[Column]))

//RETURN IF(__min = _index, SUM(shipment[Remaining]) + SUM(shipment[Calculated Column]), "Go")

VAR _final = SWITCH(TRUE(),
 __min = BLANK(), 0,
 __min = _index, -1 * CALCULATE(SUM(shipment[Calculated Column])),
 __min < _index, CALCULATE(SUM(shipment[Remaining])))


VAR __table = SUMMARIZE(shipment,shipment[location],"__value",_final)

 RETURN IF(HASONEVALUE(shipment[location]),_final,SUMX(__table,[__value]))

Any help would be appreciated!

[v-zhenbw-msft]

Hi @thmonte ,

This seems to be an incorrect Total issue.

We would suggest that you start a new thread (and mark the other as answered) since this is a different topic.
You will have others more experienced in this specific issue be able help out if the title reflects the new issue.

It is best to provide some sample data, which will better let others understand your issue.

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.

[Greg_Deckler]

@thmonte - Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

That said, perhaps check out Days of Supply for what I believe is a similar situation: https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318

Also, this generalized While Loop might also help: https://community.powerbi.com/t5/Quick-Measures-Gallery/While-Loop/m-p/637535#M320

[thmonte]

Cool thanks @Greg_Deckler I will check it out!