Can not calculate the running total with RANK function?

[Yiyi]

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_Count	Rank
Media 1	1233	1
Media 2	744	2
Media 3	532	3
Media 4	132	4
Media 5	132	5

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 · Accepted solution]

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.