Forum Discussion

learner03's avatar
learner03
Post Partisan
3 years ago
Solved

Filter based on Sum and hide rows

I have following table as a report that displays as below-   and have an item slicer as belowto select the item code-   I need to do the fuctionality as such that when the uder ...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi learner03 ,

    Here's my solution. Create a calculated column.

    Flag =
VAR _Max =
    MAXX (
        FILTER ( 'Table', 'Table'[ItemCode] = EARLIER ( 'Table'[ItemCode] ) ),
        'Table'[Location Qty]
    )
VAR _Sum =
    SUMX (
        FILTER (
            'Table',
            'Table'[ItemCode] = EARLIER ( 'Table'[ItemCode] )
                && 'Table'[Location Qty] <= EARLIER ( 'Table'[Location Qty] )
        ),
        'Table'[Location Qty]
    )
RETURN
    IF ( _Sum <= _Max, 1, 0 )

    Result:

    If you want to show the items in a visual, put the new column in visual filter and set to1, get the correct result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

learner03's avatar
learner03
Post Partisan
3 years ago

v-yanjiang-msft because the capacity of the location is 128 and if 124 and 94 are added, it will be more than 128 which cannot be done.

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi learner03 ,

Here's my solution. Create a calculated column.

Flag =
VAR _Max =
    MAXX (
        FILTER ( 'Table', 'Table'[ItemCode] = EARLIER ( 'Table'[ItemCode] ) ),
        'Table'[Location Qty]
    )
VAR _Sum =
    SUMX (
        FILTER (
            'Table',
            'Table'[ItemCode] = EARLIER ( 'Table'[ItemCode] )
                && 'Table'[Location Qty] <= EARLIER ( 'Table'[Location Qty] )
        ),
        'Table'[Location Qty]
    )
RETURN
    IF ( _Sum <= _Max, 1, 0 )

Result:

If you want to show the items in a visual, put the new column in visual filter and set to1, get the correct result:

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

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