Forum Discussion

Yiyi's avatar
Yiyi
Icon for Helper I rankHelper I
3 years ago
Solved

Can not calculate the running total with RANK function?

Hello all, 

 

I have created a summazied table for each media with its unique item count. I added rank as well for the purpose of calculating running total ( many media have the same value thus using rank with distinct number can make sure all numbers are counted in running total). 

 

Dax for the new table: 

 

New Table = 

VAR SummarizedTable= SUMMARIZE(
            ALLSELECTED('Main Data'),
            'Main Data'[Media],
            "Unique_Item_Count", DISTINCTCOUNT('Main Data'[HeadlineID])
        )
RETURN 
ADDCOLUMNS(
    SummarizedTable,
    "Rank",
    RANK (
    DENSE,
    SummarizedTable,
    ORDERBY ( [Unique_Item_Count], DESC, 'Main Data'[Media], ASC ),
    DEFAULT
)
)

 

The newly created table looks fine to me: 

Media Unique_Item_CountRank 
Media 112331
Media 27442
Media 3 5323
Media 41324
Media 5 1325

 

However, based on such logic, when I try to create a running total and the percentage of running total / total_unique_item, it just doesn't work. The error message said that in VAR CumulativeSum that can not find the name [Unique_item_Count]. 

 

 

Pareto Test = 
VAR Total_Unique_Item = 
    CALCULATE(
        DISTINCTCOUNT('Main Data'[HeadlineID]),
        ALLSELECTED('Main Data')
    )
VAR SummarizedTable = 
    SUMMARIZE(
    ALLSELECTED('Main Data'),
    'Main Data'[Media],
     "Unique_Item_Count", DISTINCTCOUNT('Main Data'[HeadlineID])
     )
VAR Summarizedtable_with_ranking = 
    ADDCOLUMNS(
    SummarizedTable,
    "Rank",
    RANK (
    DENSE,
    SummarizedTable,
    ORDERBY ( [Unique_Item_Count], DESC, 'Main Data'[Media], ASC ),
    DEFAULT)
)

VAR CumulativeSum = CALCULATE([Unique_Item_Count],FILTER(ALL(Summarizedtable_with_ranking),[Rank] <= MAXX(Summarizedtable_with_ranking, [Rank])))

RETURN 
CumulativeSum/Total_Unique_Item

 

 

Can you help me to understand what went wrong? Thanks a lot for your help! 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Yiyi ,

     

    According to your statement, I think [Unique_Item_Count] should be a column in your virtual table. So we couldn't use it directly in CALCULATE() without any aggregation.

    Measure:

    Pareto Test =
    VAR Total_Unique_Item =
        CALCULATE (
            DISTINCTCOUNT ( 'Main Data'[HeadlineID] ),
            ALLSELECTED ( 'Main Data' )
        )
    VAR SummarizedTable =
        SUMMARIZE (
            ALLSELECTED ( 'Main Data' ),
            'Main Data'[Media],
            "Unique_Item_Count", DISTINCTCOUNT ( 'Main Data'[HeadlineID] )
        )
    VAR Summarizedtable_with_ranking =
        ADDCOLUMNS (
            SummarizedTable,
            "Rank",
                RANK (
                    DENSE,
                    SummarizedTable,
                    ORDERBY ( [Unique_Item_Count], DESC, 'Main Data'[Media], ASC ),
                    DEFAULT
                )
        )
    VAR CumulativeSum =
        SUMX (
            FILTER (
                Summarizedtable_with_ranking,
                [Rank]
                    <= MAXX (
                        FILTER ( Summarizedtable_with_ranking, [Media] = MAX ( 'Main Data'[Media] ) ),
                        [Rank]
                    )
            ),
            [Unique_Item_Count]
        )
    RETURN
        DIVIDE ( CumulativeSum, Total_Unique_Item )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Yiyi ,

     

    According to your statement, I think [Unique_Item_Count] should be a column in your virtual table. So we couldn't use it directly in CALCULATE() without any aggregation.

    Measure:

    Pareto Test =
    VAR Total_Unique_Item =
        CALCULATE (
            DISTINCTCOUNT ( 'Main Data'[HeadlineID] ),
            ALLSELECTED ( 'Main Data' )
        )
    VAR SummarizedTable =
        SUMMARIZE (
            ALLSELECTED ( 'Main Data' ),
            'Main Data'[Media],
            "Unique_Item_Count", DISTINCTCOUNT ( 'Main Data'[HeadlineID] )
        )
    VAR Summarizedtable_with_ranking =
        ADDCOLUMNS (
            SummarizedTable,
            "Rank",
                RANK (
                    DENSE,
                    SummarizedTable,
                    ORDERBY ( [Unique_Item_Count], DESC, 'Main Data'[Media], ASC ),
                    DEFAULT
                )
        )
    VAR CumulativeSum =
        SUMX (
            FILTER (
                Summarizedtable_with_ranking,
                [Rank]
                    <= MAXX (
                        FILTER ( Summarizedtable_with_ranking, [Media] = MAX ( 'Main Data'[Media] ) ),
                        [Rank]
                    )
            ),
            [Unique_Item_Count]
        )
    RETURN
        DIVIDE ( CumulativeSum, Total_Unique_Item )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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