Merge Data in a filter table

[LukeA]

Hi all,

Is there a way to merge data that has the same text in Month and Item Name as it is throwing out my Min result (as some has 0)

For example, Hi-Viz trousers would return 2 data points (1 for September 2021 and 1 for October 2021) instead of returning the 14 it is now. This table has been filtered down from a bigger list of data and the returning value in stock on hand would be 16 for september and 14 for October

[LukeA]

Hi @Icey,

Thank you for this however, I need to showcase the minimum amount of full uniform packs as a sumerised number (as seen below).

I need a way to reurn the minimum amount of full kit bags we have ready to deploy. The issue has been due to the duplicate Hi-Viz trousers not merging together. To get the table seen above I used a filter from the main data to seperate the uniform down to each component.

Then I did a union to merge all kit into a single table. From that table I filtered again by the size.

Is there a way to do this or would I need to do another sum measure for the Hi-Viz trousers and add them onto the final MIN measure?

Best regards,

Luke

[Icey · Accepted solution]

Hi @LukeA ，

Have you tried to create measures or columns to get what you want? Such as:

Sum - Measure =
CALCULATE (
    SUM ( 'Table'[Stock on Hand] ),
    ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Size], 'Table'[Item name] )
)

Sum - Column =
CALCULATE (
    SUM ( 'Table'[Stock on Hand] ),
    FILTER (
        'Table',
        'Table'[Month] = EARLIER ( 'Table'[Month] )
            && 'Table'[Size] = EARLIER ( 'Table'[Size] )
            && 'Table'[Item name] = EARLIER ( 'Table'[Item name] )
    )
)

If this isn't what you want, it would be better to share a sample .pbix file for test. Please don't contain any sensitive information.

Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey

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

[Icey]

Hi @LukeA ,

Please set "Sum" for "Stock on hand" instead of "Minimum".

Best Regards,

Icey

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