Forum Discussion

LukeA's avatar
LukeA
Frequent Visitor
4 years ago
Solved

Merge Data in a filter table

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 ...
Need Help
  • Icey's avatar
    Icey
    4 years ago

    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.

LukeA's avatar
LukeA
Frequent Visitor
4 years ago

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's avatar
    Icey
    Icon for Community Support rankCommunity Support
    4 years ago

    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.