DAX divide two tables (power pivot)

[Kevin_]

Hi, 

I'm been stuck on this all day and hoping someone might have a suggestion to fix.   I'm trying to divide values in two different tables that intersect both Month and LocationIndex, and exclude all other data from subtotals. Ending measure is  'Cost per Route Day' and excludes all the grey data shown in the tables below from pivot totals.

I have two fact tables, both are connected to the same Calendar dimension and Location dimension tables.

I figured out a working solution using Intersect, but it doesn't feel elegant and it bypasses relationships. How can this be calculated without bypassing relationships?  Any help or advice is welcomed!

=VAR _vLastPLDate
	=CALCULATE(
		EOMONTH(MAX(PLData[Date]),0),
		ALL(PLData)
	)

VAR _vNumerator = 
	CALCULATE(
		[PL Loaded Cost],
		INTERSECT(
			ALL(PLData[KPI LocationIndex]),
			VALUES(HaulingData[KPI LocationIndex])
		)
	)

VAR _vDenominator = 
	CALCULATE(
		[Route Days],
		HaulingData[Date]<=_vLastPLDate
	)

VAR _vResult =
	IF(
		NOT ISBLANK(_vNumerator) && NOT ISBLANK(_vDenominator),
		DIVIDE ( _vNumerator, _vDenominator, 0 )
	)
RETURN
    _vResult

[ryan_mayu]

@Kevin_ 

pls see the attachment below to check if this is what you want.

[Kevin_]

@ryan_mayu ,

Thank you for your response and for posting a pbix with the data laidout! 

Your recommended measure

Measure = CALCULATE(sum('PLDATA'[PL Date]),PLDATA[KPI Locationindex]<3)

The approach used to filter index less than 3 wouldn't work for my situation because the issue is not about limiting any specific index location, but rather about limiting all index locations where both PL Data and Hauling Data are not present for future and prior months.

For example, PLData is cost data and is only available the first day of the month. Hauling Data is refreshed daily and current month data wont be matched to cost data until the first of the following month (August 1, 2022)

Additionally, many locations are missing hauling data or P&L data on random months and I need to exclude these from the calculation so the totals work correctly. 

Below shows real data and the issue

• PL Loaded Cost is a measure in table PLData
• Route Days is a measure in table HaulData
• both tables share common dimensionality with both KPI Location Index and Calendar (thank you @amitchandak for teaching me proper terminology) 
• Formula 1 is the long measure from my original post using INTERSECT
• Formula 2 is @amitchandak recommended formula except I never explained both items were measures rather than columns so I removed the SUM( )

=DIVIDE([PL Loaded Cost],[Route Days],0)​

• Only the red boxed in data should factor into the output measure
• Blue boxed data does not have a match in the opposite table and should be excluded in the measure
• Yellow highlight shows totals incorrectly including both red and blue data

Any ideas how to write a formula to match similar functionality as Formula 1 and leveraging common dimensionality? This is my first time working with more than a single fact table so I haven't worked through situations like this yet.

Many thanks for your help and suggestions!

[ryan_mayu]

@Kevin_ 

maybe you can try this.

we can filter the date in Haul table and check if the index in Haul table is in PLDATA

Measure = 
VAR _index=distinct(Hauldata[KPI Locationindex])
return CALCULATE(sum('PLDATA'[PL Date]),PLDATA[KPI Locationindex] in _index)

[Kevin_]

Ryan,

Sorry for the slow response. I wasn't able to figure out to implement filtering by date shown in your example. 

This code give an error:
"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

=VAR _vLastPLDate
	=CALCULATE(
		EOMONTH(MAX(PLData[Date]),0),
		ALL(PLData)
	)
VAR _vIndex = DISTINCT(HaulingData[KPI LocationIndex])
VAR _vNumerator = 
	CALCULATE(
		[PL Loaded Cost],
		CALCULATE(SUM(PLData[Date]),
			FILTER(VALUES(PLData[KPI LocationIndex]), PLData[KPI LocationIndex]IN _vIndex)
			)
		)

VAR _vDenominator = 
	CALCULATE(
		[Route Days],
		HaulingData[Date]<=_vLastPLDate
	)

VAR _vResult =
	IF(
		NOT ISBLANK(_vNumerator) && NOT ISBLANK(_vDenominator),
		DIVIDE ( _vNumerator, _vDenominator, 0 )
	)
RETURN
    _vResult

I did modify it slightly and it works, but I think the mod only swapped INTERSECT for IN from the original formula. I'm not sure if this uses common dimensionality or not.

=VAR _vLastPLDate
	=CALCULATE(
		EOMONTH(MAX(PLData[Date]),0),
		ALL(PLData)
	)

VAR _vNumerator = 
	CALCULATE([PL Loaded Cost],
		FILTER(VALUES(PLData[KPI LocationIndex]), PLData[KPI LocationIndex]IN DISTINCT(HaulingData[KPI LocationIndex]))
		)


VAR _vDenominator = 
	CALCULATE(
		[Route Days],
		HaulingData[Date]<=_vLastPLDate
	)

VAR _vResult =
	IF(
		NOT ISBLANK(_vNumerator) && NOT ISBLANK(_vDenominator),
		DIVIDE ( _vNumerator, _vDenominator, 0 )
	)
RETURN
    _vResult

Is their a different way I should add your date filter suggestion into the code?

[amitchandak]

@Kevin_ , When you have common dimensions use them in visual and simply use a measure like

divide(sum(PLData[PL Loaded Cost]) , Sum(HaulingData[RouteDay]))

[Kevin_]

Amit, 

Thank you for your response. Please see response here link to response