Can not calculate the running total with RANK function?

Yiyi — Wed, 02 Aug 2023 07:34:16 GMT

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!

Re: Can not calculate the running total with RANK function?

Anonymous — Fri, 04 Aug 2023 06:43:13 GMT

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.

topic Re: Can not calculate the running total with RANK function? in DAX Commands and Tips

Can not calculate the running total with RANK function?

Re: Can not calculate the running total with RANK function?