Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculated table with additional calculated column

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.

"CALCULATETABLE"
ADDCOLUMNS
DAX SUMMARIZE
RANKX
union
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.