How to calculate minimum of a value from a group of similar values in other column ?

[hozefamk]

I have a field name material and Kitting quantity, here i have filtered for only one particular material type, there are hundreds of different material arranged randomly in row. I have another field kitting quantity where values are different for even same material quantity. I want to create a new row, which should calculate the minimum value of Kitting quantity out of all similar material and print the same correspondance to them in the new column.

Field highlighted in Yellow are my given field and higlighted in blue is the desired output.

P.S: Material can be arranged randomly in any order so Filter(Table(field)=Earlier(Table(field)) is useless in this case and I am looking to solve in Power Query using Custom Colum.

[v-yulgu-msft · Accepted solution]

Hi @hozefamk,

Please refer to below DAX formula to create a calculated column.

Min Column Quantity =
CALCULATE (
    MIN ( Table[Kitting quantity] ),
    ALLEXCEPT ( Table, Table[Material] )
)

Best regards,

Yuliana Gu

[Turnipface]

It worked in 2 ways in a small test that you can see here as .pbix

Option 1 was to display it as a table and measure the quantity as "Minimum"

Option 2 was to Add a new column with formula =  MIN(Kitting quantity)

Do any of these work for what you're trying to do?

[hozefamk]

As per your solution you get minimum value same for all rows. from your application i need minimum value to be 3 for wood and 4 for Stone. 

[Turnipface]

Fair point...i guess i should have checked before sending 🙂
It does seem to work as a Measure with the same formula, but not as a column. I can't seem to get it to work as a column.
Perhaps somebody else can help here.

[Anonymous]

Hi @hozefamk

What is source of this data? Is it SQL Server Database?

Thanks,

Suguna.

[hozefamk]

I am extracting data from Excel