help with complex Measure

Hey Anonymous ,

 

here is a measure that considers more that one grouping columns:

 

 

SUMX across MAX using SUMMARIZE = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'Table'
            , 'Table'[ID School]
            , 'Table'[Level]
        )
        , "MaxQty" , CALCULATE( MAX( 'Table'[Qty] ) )
    )
    , [MaxQty]
)

 

 

If I need more than one column inside my iterator table I use SUMMARIZE(...) or table functions that are allowing me to compose tables that are more complex. If there is just one column that determines the table, I use VALUES.

The result:

Hopefully, this provides what you need to tackle your challenge.

 

So, back to using SUMMARIZECOLUMNS vs SUMMARIZE, depending on your requirements, I would recommend using SUMMARIZECOLUMNS as it is optimized and for this reason, it will perform better, sometimes you will be able to notice this advantage, sometimes you won't, as this will depend on the size of your data.

But, of course, things can become more complex, as the next screenshot shows:

In the 2nd line of visuals I use the measure "SUMX across MAX using SUMMARIZECOLUMNS":

 

SUMX across MAX using SUMMARIZECOLUMNS = 
SUMX(
    SUMMARIZECOLUMNS(
        'Table'[ID School]
        , 'Table'[Level]
        , "MAXQty" , MAX( 'Table'[Qty] )
    )
    , [MAXQty]
)

 

The measure works inside the card visual but breaks inside the table visual.

 

Explaining why it breaks requires more space than is available here and is already done at least to some extent by the article I mentioned in my previous reply to KNP . Learning also means developing habits by using patterns, habits then will help us to apply the learned things faster. For this reason, I developed the habit to use SUMMARIZE over SUMMARIZECOLUMNS. Knowing that SUMMARIZE is not as fast as SUMMARIZECOLUMNS.

When I work with large datasets and every millisecond counts, I sometimes write measures just for a single visual, these moments are rare and come up with other problems like model complexity.

 

Regards,

Tom