Forum Discussion

afbraga66's avatar
afbraga66
Icon for Helper III rankHelper 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 ...
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 )
)
johnt75's avatar
johnt75
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper 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
      Icon for Super User rankSuper 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.