Forum Discussion
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_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!
- Anonymous3 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
- AnonymousNot 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.