Find top n number of Projects with respect to another column and each month

[g_js]

1-First part is to find top n projects with highest spends each month (may be create a column that tells if the project is in top n)
2-Second part is to find which sub project is spending the most in each project among the top n projects we found in fist part (may be create a column that tells if condition satisfies or not)

Project     Sub-Project         Spend     Month     |    Answer 1(n=1)            Answer 2(n=1) 

Abc             123                   $2343         jan       |            false                        false

bcd              234                  $1232         jan       |           false                        false

abc              234                   $2000         Feb     |              true                         true

abc              234                   $2100         Jan     |             false                         false

cde              345                   $5000         Jan     |               true                         true

cde              346                  $2300         Jan      |              true                         false

Thanks for the help guys

[g_js]

1-First part is to find top n projects with highest spends each month (may be create a column that tells if the project is in top n)
2-Second part is to find which sub project is spending the most in each project among the top n projects we found in fist part (may be create a column that tells if condition satisfies or not)

Project     Sub-Project         Spend     Month     |    Answer 1(n=1)            Answer 2(n=1) 

Abc             123                   $2343         jan       |            false                        false

bcd              234                  $1232         jan       |           false                        false

abc              234                   $2000         Feb     |              true                         true

abc              234                   $2100         Jan     |             false                         false

cde              345                   $5000         Jan     |               true                         true

cde              346                  $2300         Jan      |              true                         false

Thanks for the help guys

[Icey · Accepted solution]

Hi @g_js ,

It is suggested to create calculated measures instead of calculated column. With measures, we can create a what-if parameter to dynamically change the TopN value.

Measure 1 = 
VAR TOPN_ = [TOPN Value]
VAR t1 =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month] = MAX ( 'Table'[Month] ) ),
        [Project],
        "Sum_", SUM ( 'Table'[Spend] )
    )
VAR t2 =
    TOPN ( TOPN_, t1, [Sum_], DESC )
RETURN
    IF ( MAX ( 'Table'[Project] ) IN SUMMARIZE ( t2, [Project] ), "true", "false" )

Measure 2 = 
VAR TOPN_ = [TOPN Value]
VAR t1 =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Month] = MAX ( 'Table'[Month] )
                && 'Table'[Project] = MAX ( 'Table'[Project] )
                && [Measure 1] = "true"
        ),
        [SubProject],
        "Sum_", SUM ( 'Table'[Spend] )
    )
VAR t2 =
    TOPN ( TOPN_, t1, [Sum_], DESC )
RETURN
    IF (
        MAX ( 'Table'[SubProject] ) IN SUMMARIZE ( t2, [SubProject] ),
        "true",
        "false"
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.