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