Sum of Minimum QTY in each Category from Summarize Table

[vishal097]

Hello Community,

I have requirement to get the sum of minimum QTY in each SFC (where it met the time target) in current filter context

I have data like 

So to obtain this I have written a measure like 

This was working fine until no other filter got used from report apart from SFC

but when User used other filter it gave incorrect result as 6 instead of 10

Due to which my % calculate got incorrect as I have to use this value in Nominator

Reason to get 6 as result because in summarize it got only 6 values 

so I tried to add 3 more columns in Summarize as

but this give sum of all QTY due to which I am getting duplicate values as there could be more that 1 record for same SFC

what solution I find for this is to get sum of minimum QTY in each SFC but from this Summarize table 

This is where I am not able to write measure.

Can someone please help.

[tamerj1 · Accepted solution]

Hi @vishal097 
Please try

Units Produced Within Standard Time = 
SUMX (
    VALUES ( Fact_DailyMgmt_ThroughputTime_Detail[SFC] ),
    CALCULATE (
        MAXX (
            SUMMARIZE (
                Fact_DailyMgmt_ThroughputTime_Detail,
                Fact_DailyMgmt_ThroughputTime_Detail[SFC],
                Fact_DailyMgmt_ThroughputTime_Detail[START_TIME],
                Fact_DailyMgmt_ThroughputTime_Detail[END_TIME],
                Fact_DailyMgmt_ThroughputTime_Detail[Wait_Time_Type]
            ),
            CALCULATE (
                VAR QTY =
                    FIRSTNONBLANK ( Fact_DailyMgmt_ThroughputTime_Detail[QTY], 0 )
                VAR IsEligible =
                    IF (
                        SUM ( Fact_DailyMgmt_ThroughputTime_Detail[Throughput_Time] )
                            <= SUM ( Fact_DailyMgmt_ThroughputTime_Detail[ERP_Target] ),
                        1,
                        0
                    )
                RETURN
                    QTY * IsEligible
            )
        )
    )
)

[tamerj1]

Hi @vishal097 

you can nest MINX inside SUMX iterating twice over the summary table. Something like 

SUMX (

SummartTable1, -- without the aggregated columns 

CALCULATE ( 

MINX (

SummartTable2, -- with the aggregated columns 

[@Column1] * [@Column2]
)

)

)

Storing the summary table in a variable won't work die the need to apply context transition. 

[vishal097]

Hello Tamer,

Thanks for looking into this as you suggested I have made the changes in code but not it showing 0 instead of actual values 

Units Produced Within Standard Time 2 =

VAR Result =

SUMMARIZE (

Fact_DailyMgmt_ThroughputTime_Detail,

Fact_DailyMgmt_ThroughputTime_Detail[SFC],

Fact_DailyMgmt_ThroughputTime_Detail[START_TIME],

Fact_DailyMgmt_ThroughputTime_Detail[END_TIME],

Fact_DailyMgmt_ThroughputTime_Detail[Wait_Time_Type]

)

VAR Result2 =

SUMMARIZE (

Fact_DailyMgmt_ThroughputTime_Detail,

Fact_DailyMgmt_ThroughputTime_Detail[SFC],

Fact_DailyMgmt_ThroughputTime_Detail[START_TIME],

Fact_DailyMgmt_ThroughputTime_Detail[END_TIME],

Fact_DailyMgmt_ThroughputTime_Detail[Wait_Time_Type],

"@QTY", FIRSTNONBLANK ( Fact_DailyMgmt_ThroughputTime_Detail[QTY], 0 ),

"@IsEligible",

IF ( SUM ( Fact_DailyMgmt_ThroughputTime_Detail[Throughput_Time] )

<= SUM ( Fact_DailyMgmt_ThroughputTime_Detail[ERP_Target] ),

1, 0

)

)

VAR R2 =

SUMX ( Result, CALCULATE ( MINX ( Result2, [@QTY] * [@IsEligible] ) ) )

RETURN

R2

also if I replace @IsEligible result as 1, blank() then i am getting sum of all QTY but we want some of min qty in each SFC as there are more than 1 record for one SFC

[tamerj1]

@vishal097 

I specifically I asked not to store the summary table in a variable. Please try

Units Produced Within Standard Time 2 =
SUMX (
    SUMMARIZE (
        Fact_DailyMgmt_ThroughputTime_Detail,
        Fact_DailyMgmt_ThroughputTime_Detail[SFC],
        Fact_DailyMgmt_ThroughputTime_Detail[START_TIME],
        Fact_DailyMgmt_ThroughputTime_Detail[END_TIME],
        Fact_DailyMgmt_ThroughputTime_Detail[Wait_Time_Type]
    ),
    CALCULATE (
        MINX (
            SUMMARIZE (
                Fact_DailyMgmt_ThroughputTime_Detail,
                Fact_DailyMgmt_ThroughputTime_Detail[SFC],
                Fact_DailyMgmt_ThroughputTime_Detail[START_TIME],
                Fact_DailyMgmt_ThroughputTime_Detail[END_TIME],
                Fact_DailyMgmt_ThroughputTime_Detail[Wait_Time_Type],
                "@QTY", FIRSTNONBLANK ( Fact_DailyMgmt_ThroughputTime_Detail[QTY], 0 ),
                "@IsEligible",
                    IF (
                        SUM ( Fact_DailyMgmt_ThroughputTime_Detail[Throughput_Time] )
                            <= SUM ( Fact_DailyMgmt_ThroughputTime_Detail[ERP_Target] ),
                        1,
                        0
                    )
            ),
            [@QTY] * [@IsEligible]
        )
    )
)

[vishal097]

@tamerj1 

This work on filters apart from SFC

but when I select one particular SFC then should show Sum of Min QTY which will be same as 1

because we are calculating Min QTY in each SFC.

but instead of 1 it is showing some weird value as 11

Let know if you need more clarification on problem, will pinned you in message 

[tamerj1]

@vishal097 

Please try it this way

Units Produced Within Standard Time 2 =
SUMX (
    SUMMARIZE (
        Fact_DailyMgmt_ThroughputTime_Detail,
        Fact_DailyMgmt_ThroughputTime_Detail[SFC],
        Fact_DailyMgmt_ThroughputTime_Detail[START_TIME],
        Fact_DailyMgmt_ThroughputTime_Detail[END_TIME],
        Fact_DailyMgmt_ThroughputTime_Detail[Wait_Time_Type]
    ),
    CALCULATE (
        VAR QTY =
            FIRSTNONBLANK ( Fact_DailyMgmt_ThroughputTime_Detail[QTY], 0 )
        VAR IsEligible =
            IF (
                SUM ( Fact_DailyMgmt_ThroughputTime_Detail[Throughput_Time] )
                    <= SUM ( Fact_DailyMgmt_ThroughputTime_Detail[ERP_Target] ),
                1,
                0
            )
        RETURN
            QTY * @IsEligible
    )
)

[vishal097]

@tamerj1 

This is giving same result as 11 for one perticular SFC instead of 1

however for other filter it is giving correct result.

[tamerj1]

@vishal097 

What is the code fro [Qty] & [is eligible]?

[vishal097]

It is coming from same QTY column only thing is we are doing that that perticular row met the target in terms of time 

if met then in IsEligible it will give 1 or else 0 

so let's say if it met the target it will give 1 and in QTY it will give first nonblank QTY we are doing mutiplication so that we will get the same QTY

but if it does met target then it will become like 1*0 which will be 0 so this would not get count in calculation.

shall we connect over call so that I could better explain you the case