Forum Discussion
DAX divide two tables (power pivot)
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
6 Replies
- amitchandak
Super User
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]))
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!
