Calculated table with additional calculated column

[Anonymous]

Dear Group,

I would like to create a table that will be based on Union as below:

Ranking =

Var _Plant1 = CALCULATETABLE(SUMMARIZE('For Plants','For Plants'[Customer],'For Plants'[Plant abb],"Sum of act",sum('For Plants'[Actuals in LC])),FILTER('For Plants','For Plants'[Plant abb]="Plant1"))

var _summary = ADDCOLUMNS(_Plant1,"Rank",RANKX(ALL(_Plant1),_Plant1[Sum of act],,DESC,Dense))

Var _Plant2 = CALCULATETABLE(SUMMARIZE('For Plants','For Plants'[Customer],'For Plants'[Plant abb],"Sum of act",sum('For Plants'[Actuals in LC])),FILTER('For Plants','For Plants'[Plant abb]="Plant2"))

var _summary2 = ADDCOLUMNS(_Plant2,"Rank",RANKX(ALL(_Plant2),_Plant2[Sum of act],,DESC,Dense))

RETURN union(_summary,_summary2)

Unfortunately, this doesn't work. It works if I do it step by step - first create a separate table per plant, then add a column with ranking and then create a separate table with UNION, but as I have 10 plants, I would like to avoid creation of 11 tables.

The overall idea is to give rank to per customer per plant based on sum of actuals.

Could you please advise how to do it all at one? Thank you in advance.

[Anonymous]

Hi @Anonymous ,

I created test data and tested the formula provided by bhanu_gautam and the results should meet your needs.

Ranking = 
VAR _AllPlants = 
    SUMMARIZE(
        'For Plants',
        'For Plants'[Customer],
        'For Plants'[Plant abb],
        "Sum of act", SUM('For Plants'[Actuals in LC])
    )
VAR _RankedPlants = 
    ADDCOLUMNS(
        _AllPlants,
        "Rank", RANKX(
            FILTER(_AllPlants, [Plant abb] = EARLIER([Plant abb])),
            [Sum of act],
            ,
            DESC,
            DENSE
        )
    )
RETURN _RankedPlants

Best Regards,
Adamk Kong

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

[bhanu_gautam · Accepted solution]

@Anonymous , Without creating multiple intermediate tables, you can use a single DAX expression that combines the steps. 

Ranking =
VAR _AllPlants =
CALCULATETABLE(
SUMMARIZE(
'For Plants',
'For Plants'[Customer],
'For Plants'[Plant abb],
"Sum of act", SUM('For Plants'[Actuals in LC])
)
)

VAR _RankedPlants =
ADDCOLUMNS(
_AllPlants,
"Rank", RANKX(
FILTER(_AllPlants, 'For Plants'[Plant abb] = EARLIER('For Plants'[Plant abb])),
[Sum of act],
,
DESC,
DENSE
)
)

RETURN _RankedPlants