Forum Discussion

newgirl's avatar
newgirl
Post Patron
5 years ago
Solved

Matrix Subtotals

Hi, everyone!

 

I was able to come up with this visual. I understand why it's showing 0.83 as the Total but how can I modify or what additional measure to create so that the Total will show the sum of other Target Trips from the other columns?

 

 

 

This is the measure used:

 

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 )
    )
)

 

 

and here is my sample pbix:

 

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi newgirl 

    You can add a new measure by Target Trips Everyday measure. And use the new measure to be the value in matrix visual.

    Measure = 
SUMX('Calendar',[Target Trips Everyday])

    Result:

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    newgirl , try like

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

    )
    )

     

    • newgirl's avatar
      newgirl
      Post Patron
      5 years ago

      Hi amitchandak !

       

      I tried your formula in the file

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

       

       

      but this is the output:

       

       

       

      The Column Subtotals are still showing 0.83 instead of adding up and the Rows Subtotals did not add up as well.

      Can you still help me modify the formula? In the original one, the Rows Subtotals are what I want since they add up the rows; it was the Column Subtotals I have a problem with.

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        newgirl ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.