Forum Discussion
Helper IHelp with Multiple Table Measures with and without Aggregate
MRGMSR , what column in your 'Rail KPI Monthly' table holds the year and month? Is it the [Month] column? What format is that column? What you need to be able to do is have a column in 'Rail KPI Monthly' that can be mapped onto the corresponding month in 'Fiscal Calendar'. Let's assume that in both tables you have a YearMonth column that is in the format YYYYMM.
I notice in the image of your data model that the relatinship between 'Fincal Calendar' and 'Rail KPI Monthly' is dotted, which means it is 'Inactive', so it will not affet how the measures work. The direction of the relationship is also the wrong way - normally you would have a relationship flowing from the Date dimension to the fact table. I assume you have not made the relationship active because there was no correct column to map between them, it that right?
In any case, without changing the relationships, I think your [Rail MDBF] measure needs to be something like this:
Rail MDBF = VAR vSelectedYYYYMM = SELECTEDVALUE('Fiscal Calendar'[YYYYMM]) RETURN CALCULATE( AVERAGE('Rail KPI Monthly'[MDBF]), ALL('Rail KPI Monthly'), 'Rail KPI Monthly'[YYYYMM] = vSelectedYYYYMM )It would help if you could also post a sample of the contents of the 'Rail KPI Monthly' file.
Helper IHopefully, I can explain the issue. It is frustrating me so much that I can't even explain it...
TABLE1 shows Miles Between Road Calls (MBRC) for each route (multiple rows) for each Service by month except for Rail because that MBRC is in another table (TABLE2) that shows the MBRC by Month (one single row for each month). In TABLE1 Rail shows MBRC as 0
To get the MBRC for each service in TABLE1, I average Miles Between Road Calls of all routes for the Service in a measure AVERAGE(Mean Miles between Road Calls) and I get an average for that service
I put this measure into a SELECTEDVALUE and Switch so when a service is selected in the slicer, I get the average for that service – Works fine for all services except Rail because of the aggregate I need to use
SELECTEDVALUE(Service), SWITCH(“MetroBus", AVERAGE MBRC)
"MetroRail", AVERAGE(TABLE2)
CORRECT
INCORRECT
TABLE2 shows MBRC for 1 route for 1 Service (Rail) by month
When I want to pull the MBRC for this service in a measure, I need to use an aggregate function (AVERAGE) but because there is only one value for each month, I get either the sum of all MBRC in the column for that route or an Average of all MBRC for the month and not the value of the month
This is the data in TABLE2 that holds the MBRC of Rail. The correct value to show when MetroRail is selected from the slicer is 7831 for the month of February but the aggregate function I need to use to pull this MBRC into the SELECTEDVALUE(Services) measure averages the MBRC column and provides the average of the entire column when all I need is the actual value of the column for the month and service selected.
Regardless of the month I choose for MetroRail, I get the average or sum whichever aggregate I use for that month. For instance This is December 2020 and I get the AVERAGE of the entire MBRC column
Helper IThese are the relationships from all tables to calendar and services tables