Helo with Dax formula for calculating requirement Independent from table column

[ValeriaBreve]

Hello, 

I have a table as follows:

Date	ID & Text	Pack Type	# Requirements per Pallet	# Req CP3
8/11/2025	1	A	20
8/11/2025	2	A	10
8/11/2025	3	A	30
8/11/2025	4	B	70	10
8/12/2025	1	A	50
8/12/2025	2	A	90
8/12/2025	3	A	60
8/12/2025	4	B	250	50
8/13/2025	1	A	5
8/13/2025	2	A	90
8/13/2025	3	A	55
8/13/2025	4	B	200	50

# Req CP3 is what I need to calculate, and it is basically the total [# Requirements per Pallet] for Trade_Products[Pack Type]="B" for the selected date minus the sum of all [# Requirements per Pallet] for Trade_Products[Pack Type]="A" at the selected date.

I don't seem to be getting it right 😞

VAR SelectedDate = SELECTEDVALUE( Date_Table[Date] )

VAR PalletReq =
CALCULATE(
[# Requirements],
Trade_Products[ID & Text] = "4",
Date_Table[Date] = SelectedDate
)

//this should calculate the aggregate of all Product Types = "A" on the selected date

VAR FutsReq =
CALCULATE(
[# Requirements per Pallet],
Trade_Products[Pack Type] = "A",
Trade_Products[ID & Text] <> "4",
Date_Table[Date] = SelectedDate
)
RETURN
PalletReq - FutsReq

With this, I am getting a column that gives me the same numbers as per [# Requirements per Pallet], and not the aggregation I am looking for.

Can you help me understand which direction to take?

Thanks!

Kind regards

Valeria

[johnt75]

You can create a measure like

# Req CP3 = 
IF( SELECTEDVALUE( 'Table'[ID & Text] ) = 4,
	VAR PalletReq = 
	CALCULATE(
		SUM( 'Table'[# Requirements per Pallet] ),
		REMOVEFILTERS( 'Table' ),
		'Table'[ID & Text] = 4,
		VALUES( 'Date'[Date] )
	)
	VAR FutsReq = 
	CALCULATE(
		SUM( 'Table'[# Requirements per Pallet] ),
		REMOVEFILTERS( 'Table' ),
		'Table'[ID & Text] <> 4,
		'Pack Type'[Pack Type] = "A",
		VALUES( 'Date'[Date] )
	)
	RETURN PalletReq - FutsReq
)

See attached PBIX for reference.

[ValeriaBreve]

@johnt75 Hi John, your solution also works beautifully! A pity I cannot accept 2 solutions, I already accepted Greg's as it came first, but thank you so much - I have learned a lot from your code!

[Greg_Deckler]

@ValeriaBreve Try:

Measure = 
    VAR __Type = MAX( 'Table'[Pack Type] )
    VAR __Date = MAX( 'Table'[Date] )
    VAR __ATable = FILTER( ALL( 'Table' ), [Date] = __Date && [Pack Type] = "A" )
    VAR __Result = IF( __Type = "A", BLANK(), MAX( 'Table'[# Requirements per Pallet] ) - SUMX( __ATable, [# Requirements per Pallet] ) )
RETURN
    __Result

[ValeriaBreve]

Hi @Greg_Deckler , thank you! however I have a date table for [Date] and another dimension table for [Pack Type" so when coming to Var _ATable I was not sure how to proceed as in this one everything is supposed to be on the same table (I have 2 dimensions and a fact table).

I ended up with something like the below, which works but it looks very complicated to me and I wonder whether I could have done it in a much simpler way....

VAR SelectedDate = SELECTEDVALUE( Date_Table[Date] )

VAR PalletReqTable =

    SUMMARIZECOLUMNS(

        Date_Table[Date],

        Trade_Products[Pack Type],

        FILTER( Trade_Products, Trade_Products[[ID & Text] = "4" ),

        FILTER(

            Date_Table,

          Date_Table[Date] = SelectedDate

        ),

        "Pallet Req", -[# Requirements]

    )

VAR FutsReqTable =

    SUMMARIZECOLUMNS(

        Date_Table[Date],

        Trade_Products[Pack Type],

        FILTER(

            ALL( Trade_Products ),            

                 Trade_Products[Pack Type] = "A"

        ),

        FILTER(

            Date_Table,

          Date_Table[Date] = SelectedDate

        ),

        "Pallet Req", -[# Requirements per Pallet]

    )

VAR UnionTable = UNION( FutsReqTable, PalletReqTable )

VAR FinalSummary =

    GROUPBY(

        UnionTable,

        Date_Table[Date],

                "Total Pallet Req", SUMX( CURRENTGROUP( ), [Pallet Req] )

    )

RETURN

    {

         ROW("Max Total Pallet Req", MAXX(FinalSummary, [Total Pallet Req]))

    }

[Greg_Deckler · Accepted solution]

@ValeriaBreve So that would be:

Measure = 
    VAR __Type = MAX( 'PackDimension'[Pack Type] )
    VAR __Date = MAX( 'DateDimension'[Date] )
    VAR __ATable = FILTER( ALL( 'Table' ), [Date] = __Date && [Pack Type] = "A" )
    VAR __Result = IF( __Type = "A", BLANK(), MAX( 'Table'[# Requirements per Pallet] ) - SUMX( __ATable, [# Requirements per Pallet] ) )
RETURN
    __Result

[ValeriaBreve]

@Greg_Deckler Hi Greg, got it. I still have my 2 dimension tables - meaning that  I cannot use the [Pack Type] as is in the _ATable because it does not exit in the fact table - fact and dimension are related through Material ID. So I modified it this way:
VAR __ATable =
FILTER(
ALL( PACKED_INV ),
PACKED_INV[START_DATE] = __Date
&& RELATED( Trade_Products[Pack Type] ) = "A"
)
and it works beautifully :-). 

Thank you so much!

[bhanu_gautam]

@ValeriaBreve , Try using

DAX
VAR SelectedDate = SELECTEDVALUE(Date_Table[Date])

// Calculate the total requirements for Pack Type "B" on the selected date
VAR PalletReq =
CALCULATE(
SUM(Trade_Products[# Requirements per Pallet]),
Trade_Products[Pack Type] = "B",
Date_Table[Date] = SelectedDate
)

// Calculate the total requirements for Pack Type "A" on the selected date
VAR FutsReq =
CALCULATE(
SUM(Trade_Products[# Requirements per Pallet]),
Trade_Products[Pack Type] = "A",
Date_Table[Date] = SelectedDate
)

RETURN
PalletReq - FutsReq

[ValeriaBreve]

Hi @bhanu_gautam thank you, however this measure is not aggregating by [ID & Text] so when I use it in the table it is returning the [# Requirements per Pallet] per row/[ID & Text]. I did find a solution (as per reply to Greg Deckler) but it si quite complicated - I wonder whether I could simplify it. Anyway, thank you for taking the time to look at this!