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 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'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.

3 Replies

  • 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?