Displaying categories with no data and category calc based on existing values in the same field

[ldsmith919]

Hello there!

Here is my current data and matrix visual (sample data).  

Material	Location	Lead Time	Category	Date	Quantity
A-1	100	15	Available	11/20/2024	100
A-1	100	15	Planned Demand	1/6/2025	50
A-2	100	12	Available	11/20/2024	20
A-2	200	12	Available	11/20/2024	20
A-2	200	12	Firm Demand	11/30/2024	60
A-2	200	12	Planned Demand	11/30/2024	40
A-3	100	10	Available	11/20/2024	15
A-3	100	10	Planned Orders	12/16/2024	45
B-1	100	18	Available	11/20/2024	30
B-2	200	15	Available	11/20/2024	55
B-2	200	15	Firm Orders	12/13/2024	25
B-2	200	15	Planned Demand	12/13/2024	60
B-2	200	15	Planned Orders	1/6/2025	40
B-2	200	15	Firm Demand	1/6/2025	75
B-3	100	5	Available	11/20/2024	10
B-3	200	5	Available	11/20/2024	40
B-3	100	5	Planned Orders	11/30/2024	50
B-3	100	5	Firm Demand	12/16/2024	60

I have a couple of problems I'm trying to solve:

1.  How can I display all the categories even if there is no data for that category?  I'm fine with it displaying a zero, though I would prefer it to be blank.  It would look like this:

2.  How can I create a calculation that sums multiple other categories within an existing category?

Currently, I already have an "Available" category.  However, I would want the new available category to be calulated such that the result is New Available = Old Available + Firm Orders + Planned Orders - Firm Demand - Planned Demand.  You can see those values in the screenshot above.

Any help that can be provided would be greatly appreciated!

[DataInsights · Accepted solution]

@ldsmith919,

This can be achieved with a disconnected table such as the one below. It has no relationships with other tables. The Index column allows you to sort Category by Index.

Create measures:

Sum Quantity = SUM ( FactTable[Quantity] )

Quantity Switch = 
SWITCH (
    SELECTEDVALUE ( Category[Category] ),
    "New Available",
        CALCULATE (
            [Sum Quantity],
            FactTable[Category] IN { "Available", "Firm Orders", "Planned Orders" }
        )
            - CALCULATE (
                [Sum Quantity],
                FactTable[Category] IN { "Firm Demand", "Planned Demand" }
            ),
    CALCULATE (
        [Sum Quantity],
        TREATAS ( VALUES ( Category[Category] ), FactTable[Category] )
    )
)

In the matrix, use Category[Category] (disconnected table). Click the down arrow for Category in the Rows field well and select "Show items with no data".

[ldsmith919]

So my business users decided to add one more layer of complexity to the calculation where the "New Available" quantity is the same as the previous date (I'm using date bins) unless new demand or orders are added (red).  This would mean all date bins show up (green) and the total would reflect final state (blue).  This what the data should look like with the new calculation in place.  With the orinigal problem's complexity, the different solution I've tried using MAX, SUMX and EARLIER have not worked.

[ldsmith919]

This worked perfectly, thank you so much!