Matrix Subtotals

Hi amitchandak ! 

Here is my pbix link

https://drive.google.com/file/d/1UX0--4-V9b2Lu6g1xyMsawDEBXKGQJMQ/view?usp=sharing

Just to give context, this was my original problem https://community.powerbi.com/t5/Desktop/Cut-Off-Dates/m-p/1441355

but since no one answered, I tried to do my best to solve it, which led me to make the measure I showed in my post 

Target Trips = 
SUMX (
    VALUES (Cutoff[Off Hire MonthYear]),
    SUMX (
        FILTER (
            FleetNeg,
            Cutoff[Off Hire MonthYear] = FleetNeg[MonthYear]
        ),
        FleetNeg[Target_Trips_Abs] / ( FleetNeg[To] - FleetNeg[From] + 1 )
    )
)

However, it doesn't satisy the Columns Subtotal, which is why I had to post another question in this forum.

Thank you!

Hi amitchandak !

Please disregard the 1st reply I sent. Kindly consider this link for the sample pbix below:

https://drive.google.com/file/d/1lq1zDz8a2pbiUSkogQrNeNTCXNG24lHn/view?usp=sharing

I had to add another measure in the file because I actually need the target trips to be shown everyday. 

Target Trips Everyday = 
CALCULATE(
SUMX(SUMMARIZE(fTMS,'RealPlate List'[RealPlate], "TargetTrips", [Target Trips]), [TargetTrips]),
CROSSFILTER(fTMS[date_backinplant], 'Calendar'[Date], None))

However, the measure is still not perfect because it doesn't add up.

Basically, the purpose of the report is to present the daily target trip of the plate number based on the table FleetNeg. What makes it hard for me is the Cut Off table, since some month-end dates such as Aug 30 to 31, are considered as September cut-off. So if the user would filter the dates in the page as Aug 30 to Sep 28 (or the September cut-off period), the target trip should be looked up only for the dates of September from the FleetNeg table.

In the example visual below, the only thing wrong would be the Column Subtotals. For example, AUA 5182 should display 24.9 (or 25) in the column subtotal since it has 0.83 daily target trips x 30 days (Aug 30 to Sep 28). The 0.83 is from 25 target trips divided by 30 days (30 days of September).