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 (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

 

 

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.

3 Replies

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    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.

  • 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
      Community 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.