Forum Discussion

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

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

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 ...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    Yiyi
    Icon for Helper I rankHelper I
    3 years ago

    Super cool! It works, thanks a lot!

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

    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?