Forum Discussion

Iamnvt's avatar
Iamnvt
Continued Contributor
7 years ago
Solved

Incorrect total

hi, I know this is a classic problem that can be resolved with SUMX, but somehow, I couldn't get it done.

 

Here is the file:

https://1drv.ms/x/s!Aps8poidQa5zk5NCkQV_1tDZcbThRQ

 

I can't match the total as below:

 

thank in advance

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Iamnvt

     

    In a single step, you can do like this

     

    But i think better to create a second measure like in the earlier post

     

    Demand in LT =
IF (
    HASONEFILTER ( LT[Mat] ),
    SUMX (
        VALUES ( LT[Mat] ),
        VAR newLT =
            SUMX ( LT, LT[LT] )
        VAR filtertable =
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] >= TODAY ()
                    && 'Calendar'[Date] - TODAY ()
                        <= newLT
            )
        RETURN
            CALCULATE ( SUM ( Demand[Demand] ), filtertable )
    ),
    SUMX (
        VALUES ( LT[Mat] ),
        CALCULATE (
            SUMX (
                VALUES ( LT[Mat] ),
                VAR newLT =
                    SUMX ( LT, LT[LT] )
                VAR filtertable =
                    FILTER (
                        ALL ( 'Calendar' ),
                        'Calendar'[Date] >= TODAY ()
                            && 'Calendar'[Date] - TODAY ()
                                <= newLT
                    )
                RETURN
                    CALCULATE ( SUM ( Demand[Demand] ), filtertable )
            )
        )
    )
)

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Iamnvt

     

    You can use this MEASURE

     

    Measure =
IF (
    HASONEFILTER ( LT[Mat] ),
    [Demand in LT],
    SUMX ( VALUES ( LT[Mat] ), [Demand in LT] )
)
    • Iamnvt's avatar
      Iamnvt
      Continued Contributor
      7 years ago

      Zubair_Muhammad

      thanks for the solution.

      How can I combine the two measures: [Demand in LT], and [Measure 1] into 1 measure only?

       

      I tried to put the same formula of [Demand in LT] into [Measure 1], but it didn't give the correct result.

       

      Thanks

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        Iamnvt

         

        In a single step, you can do like this

         

        But i think better to create a second measure like in the earlier post

         

        Demand in LT =
IF (
    HASONEFILTER ( LT[Mat] ),
    SUMX (
        VALUES ( LT[Mat] ),
        VAR newLT =
            SUMX ( LT, LT[LT] )
        VAR filtertable =
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] >= TODAY ()
                    && 'Calendar'[Date] - TODAY ()
                        <= newLT
            )
        RETURN
            CALCULATE ( SUM ( Demand[Demand] ), filtertable )
    ),
    SUMX (
        VALUES ( LT[Mat] ),
        CALCULATE (
            SUMX (
                VALUES ( LT[Mat] ),
                VAR newLT =
                    SUMX ( LT, LT[LT] )
                VAR filtertable =
                    FILTER (
                        ALL ( 'Calendar' ),
                        'Calendar'[Date] >= TODAY ()
                            && 'Calendar'[Date] - TODAY ()
                                <= newLT
                    )
                RETURN
                    CALCULATE ( SUM ( Demand[Demand] ), filtertable )
            )
        )
    )
)