The running total doesn't count same values? Why :(

[Yiyi]

Hello all, 

I am new to DAX and I tried to calculate the running total for the variable of media so I can see how many media counts for 80% of all the item creation. The result is problematic as the rows with the same values were not cumulated: 

My Dax is as follows: 

Pareto =
VAR Total_Unique_Item =
   CALCULATE(
   DISTINCTCOUNT(Data[HeadlineID]),
   ALLSELECTED(Data)
    )
VAR Current_Total_Unique_Item = DISTINCTCOUNT(Data[HeadlineID])
VAR SummarizedTable =
   SUMMARIZE(
   ALLSELECTED(Data),
   Data[Media],
   "Unique_Item_Count", DISTINCTCOUNT(Data[HeadlineID])
)
VAR CumulativeSum =
   SUMX (
   FILTER(SummarizedTable, [Unique_Item_Count] >= Current_Total_Unique_Item),
   [Unique_Item_Count])
RETURN
CumulativeSum/Total_Unique_Item

I really appreciate if you could help! Thanks! 

[Anonymous · Accepted solution]

Hi @Yiyi ,

I suggest you to try code as below to create a measure.

Pareto =
VAR Total_Unique_Item =
    CALCULATE ( DISTINCTCOUNT ( Data[HeadlineID] ), ALLSELECTED ( Data ) )
VAR Current_Total_Unique_Item =
    DISTINCTCOUNT ( Data[HeadlineID] )
VAR SummarizedTable =
    SUMMARIZE (
        ALLSELECTED ( Data ),
        Data[Media],
        "Unique_Item_Count", DISTINCTCOUNT ( Data[HeadlineID] )
    )
VAR ADDRANK =
    ADDCOLUMNS (
        SummarizedTable,
        "RANK",
            RANKX ( SummarizedTable, [Unique_Item_Count],, DESC, SKIP )
                + IF (
                    COUNTX (
                        FILTER (
                            SummarizedTable,
                            [Unique_Item_Count] = EARLIER ( [Unique_Item_Count] )
                        ),
                        [Unique_Item_Count]
                    ) > 1,
                    RANKX (
                        FILTER (
                            SummarizedTable,
                            [Unique_Item_Count] = EARLIER ( [Unique_Item_Count] )
                        ),
                        [Media],
                        ,
                        ASC,
                        DENSE
                    ) - 1
                )
    )
VAR CumulativeSum =
    SUMX (
        FILTER (
            ADDRANK,
            [RANK]
                <= SUMX ( FILTER ( ADDRANK, [Media] = MAX ( Data[Media] ) ), [RANK] )
        ),
        [Unique_Item_Count]
    )
RETURN
    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.

[Yiyi]

Hello Rico, 

If you may still have some time, I would like to ask if it is possible to calculate the number of media accounting for 80% of all the items built on the code you provided. 

VAR Pareto = CumulativeSum / Total_Unique_Item 
VAR ROW_COUNT_80 = CALCULATE(COUNTROWS(SummarizedTable), FILTER( SummarizedTable, pareto < 0.8)) 
RETURN ROW_COUNT_80

I tried to add those below your code, but the number is a constant number (total amount of media). Can you kindly tell what is wrong here? 

[Yiyi]

Super cool! It works, thanks a lot!