Forum Discussion

afbraga66's avatar
afbraga66
Helper III
3 years ago
Solved

Make Total de Sum of Rows

Hello,

I am trying to make the Total row be the sum of the rows, but I'm not achieving that. These are the 2 measures.
 
Changeovers Planned =
var _a = COUNTROWS(F_ACTUAL_FROZEN_PLAN)-1

RETURN
IF(
_a = -1,
0,
_a)

 

Changeovers Planned Yesterday =
var _a =
CALCULATE(
COUNTROWS(F_ACTUAL_FROZEN_PLAN),
Dim_Calendar[Manufacturing Date]=MAX(Dim_Calendar[Manufacturing Date])-1
)

RETURN
IF(
_a = -1,
0,
_a)

 

This is the result I get, but I exptected to get 62 and 52 as totals, respectively.


How can I achieve it?

Thank you.

 

Best regards,

André

 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago
    Changeovers Planned =
SUMX (
    VALUES ( Dim_Calendar[Manufacturing Date] ),
    VAR _a =
        CALCULATE ( COUNTROWS ( F_ACTUAL_FROZEN_PLAN ) ) - 1
    RETURN
        IF ( _a = -1, 0, _a )
)

Changeovers Planned Yesterday =
SUMX (
    VALUES ( Dim_Calendar[Manufacturing Date] ),
    VAR _a =
        CALCULATE (
            COUNTROWS ( F_ACTUAL_FROZEN_PLAN ),
            Dim_Calendar[Manufacturing Date]
                = MAX ( Dim_Calendar[Manufacturing Date] ) - 1
        )
    RETURN
        IF ( _a = -1, 0, _a )
)

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago
    Changeovers Planned =
SUMX (
    VALUES ( Dim_Calendar[Manufacturing Date] ),
    VAR _a =
        CALCULATE ( COUNTROWS ( F_ACTUAL_FROZEN_PLAN ) ) - 1
    RETURN
        IF ( _a = -1, 0, _a )
)

Changeovers Planned Yesterday =
SUMX (
    VALUES ( Dim_Calendar[Manufacturing Date] ),
    VAR _a =
        CALCULATE (
            COUNTROWS ( F_ACTUAL_FROZEN_PLAN ),
            Dim_Calendar[Manufacturing Date]
                = MAX ( Dim_Calendar[Manufacturing Date] ) - 1
        )
    RETURN
        IF ( _a = -1, 0, _a )
)
    • afbraga66's avatar
      afbraga66
      Helper III
      3 years ago

      Hello johnt75 

      Can you explain using VALUES works in this case?

      I tried the SUMX with the table itself - F_ACTUAL_FROZEN_PLAN - but it didnt' work of course.

       

      Thank you.

       

      Best regards,

      André

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        VALUES returns those entries which are visible in the current filter context. In your visual at the individual row level then only 1 date is visible, so VALUES returns only that date, but at the total level then VALUES will return all the dates which are visible, obeying any slicers or filters which are affecting the result.