Solved: Formula Help

Kareus · ‎11-07-2020

How can I determine if all the rows that share a particular order # have inventory to ship Order #400001 complete.

Report Date	Order #	Scheduled Date	Description	Inventory	Items Needed	Is Their Enough Inventory On Hand To Ship Order Complete	WHICH ORDER NUMBERS
11/7/2020	400000	11/7/2020	Widget #1	1000	25	YES
11/7/2020	400001	11/7/2020	Widget #1	1000	20	YES
11/7/2020	400001	11/7/2020	Widget #2	2	4	FALSE
11/7/2020	400001	11/7/2020	Widget #3	55	11	YES
11/7/2020	400002	11/7/2020	Widget #3	55	13	YES

mahoneypat · ‎11-07-2020

Here is a column you can put on your orders table to indicate if there is sufficient inventory for each item. It also works as a measure expression that will work in a table visual with the Order # column.

Enough =
IF (
    ISBLANK (
        CALCULATE (
            COUNT ( Orders[Order #] ),
            FILTER (
                ALLEXCEPT (
                    Orders,
                    Orders[Order #]
                ),
                Orders[Inventory] < Orders[Items Needed]
            )
        )
    ),
    "Y",
    "N"
)

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

View solution in original post

mahoneypat · ‎11-07-2020

Here is a column you can put on your orders table to indicate if there is sufficient inventory for each item. It also works as a measure expression that will work in a table visual with the Order # column.

Enough =
IF (
    ISBLANK (
        CALCULATE (
            COUNT ( Orders[Order #] ),
            FILTER (
                ALLEXCEPT (
                    Orders,
                    Orders[Order #]
                ),
                Orders[Inventory] < Orders[Items Needed]
            )
        )
    ),
    "Y",
    "N"
)