Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Sum of Rows

Hi, I have a matrix table but the subtotals for each row is wrong.

 

 

The current table that i have right now is this:

 

This is my desired outcome:

 

To get the values, I've created a measure and this is my calculation:

Test =
VAR ThisDate = SELECTEDVALUE ( 'Date'[Date] )
VAR Hours =
  CALCULATE (
  (100 - SUM ( 'Hours'[Loading])) * 0.08,
  FILTER (
  'Hours',
  'Hours'[Job Start Date] <= ThisDate &&
  'Staff Booking'[End date] >= ThisDate &&
)
)
 
Return Hours

 

Need Help

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

     

     

    Test =
VAR ThisDate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR Hours =
    SUMX (
        DISTINCT ( 'Date'[Date] ),
        CALCULATE (
            ( 100 - SUM ( 'Hours'[Loading] ) ) * 0.08,
            FILTER (
                'Hours',
                'Hours'[Job Start Date] <= ThisDate
                    && 'Staff Booking'[End date] >= ThisDate
            )
        )
    )
RETURN
    Hours

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi I think you are close to the solution. However right now, i realised that the total sum of rows are "8 * number of date" 

      • v-yadongf-msft's avatar
        v-yadongf-msft
        Community Support
        3 years ago

        Hi Anonymous ,

         

        Has your problem been solved? If not solved, can you share with me some screenshots of your data after hiding sensitive information?

         

        Best regards,

        Yadong Fang