Aggregation of MINX

[joshua1990]

hello everybody!

I hope this request/ question will work without sample data.

I have a simple measure like this:

Test = 
    CALCULATE (MINX (
            FILTER (
                'Order Routing',
                'Order Routing'[Type] = 5
                    && 'Order Routing'[Code] = "KI9"
            )
        , 'Order Routing'[Quantity])
    )

This measure returns the minimum value for each Order based on the filtering above.

If I aggregate this result by a different hierarchy like Department, Area etc. I get a false value since the measure will always return the minimum value regardless of the aggregation. 

But I need a SUM for each aggregation.

For Now, I have added the SUMX like:

Test = 
    SUMX('Order Routing',CALCULATE (MINX (
            FILTER (
                'Order Routing',
                'Order Routing'[Type] = 5
                    && 'Order Routing'[Code] = "KI9"
            )
        , 'Order Routing'[Quantity])
    ))

Is this a good solution? Do you have any comments/ recommendations?

[Anonymous]

To get the desired formula you have to say explicitly which attributes are the ones for which you want to SUM the minima and which are the ones that you want to get the minima for. Only then will you be able to create the correct expression.

By the way, measures do not need to be wrapped in CALCULATE when you define them. The engine does it automatically, even though you can't see it. Second, you should not use iterators when you don't have to as this will be slower. For instance, your first measure could be written like

Test =
calculate(
	min( 'Order Routing'[Quantity] ),
	keepfilters( 'Order Routing'[Type] = 5 ),
	keepfilters( 'Order Routing'[Code] = "KI9" )
)

Third, your last measure is equivalent to this

calculate(
    sum( 'Order Routing'[Quantity] ),
    keepfilters( 'Order Routing'[Type] = 5 ),
    keepfilters( 'Order Routing'[Code] = "KI9" )
)

The reason is that you are SUMX'ing over individual rows of a table and the CALCULATE in the second argument of your formula transforms this row context into a filter context on the whole EXPANDED table. Hence, your FILTER function under MINX only sees one row and depending on whether the row satisfies the condition or not, the outcome is either the value in the field or BLANK. MINX doesn't do anything in this formula.

[joshua1990]

@Anonymous Thank you so much for your support and the comprehensive explanation!

How can I define what should be used as an aggregation level for SUM and for MIN?

The first measure works perfectly for an overview of all orders in a matrix.

But when I use the same measure just for one order, then I get blank

[joshua1990]

@Anonymous :

I think I get it:

Test =
SUMX('OrderMaster',
calculate(
	min( 'Order Routing'[Quantity] ),
	keepfilters( 'Order Routing'[Type] = 5 ),
	keepfilters( 'Order Routing'[Code] = "KI9" )
))

I have an OrderMaster table that contains for each order a single row with some basic information.

If I use the measure above the aggregation works well.

[amitchandak]

@joshua1990 ,

You need to use values to take the sum above a particular

Test = 
    SUMX(values('Order Routing'[Deaprtment]),CALCULATE (MINX (
            FILTER (
                'Order Routing',
                'Order Routing'[Type] = 5
                    && 'Order Routing'[Code] = "KI9"
            )
        , 'Order Routing'[Quantity])
    ))

[joshua1990]

@amitchandak : Thanks, But I still get the minimum value, also on Department level. Please note, I am working with an separate attribute master containing the department, area information etc.I have used SUMX (VALUES ('AttributeMaster'[Department]