Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years 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]...
"CALCULATETABLE"
ADDCOLUMNS
DAX SUMMARIZE
RANKX
union
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years 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
2 years 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.