Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Iamnvt
Continued Contributor
Continued Contributor

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:

Capture.PNG

 

thank in advance

1 ACCEPTED SOLUTION

@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 )
            )
        )
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

@Iamnvt

 

You can use this MEASURE

 

Measure =
IF (
    HASONEFILTER ( LT[Mat] ),
    [Demand in LT],
    SUMX ( VALUES ( LT[Mat] ), [Demand in LT] )
)

Regards
Zubair

Please try my custom visuals

@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

@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 )
            )
        )
    )
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.