topic Re: The running total doesn't count same values? Why :( in DAX Commands and Tips

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

Yiyi — Tue, 01 Aug 2023 11:09:04 GMT

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:

I really appreciate if you could help! Thanks!

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

Anonymous — Thu, 03 Aug 2023 07:10:34 GMT

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.

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

Yiyi — Thu, 03 Aug 2023 10:49:41 GMT

Super cool! It works, thanks a lot!

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

Yiyi — Thu, 03 Aug 2023 11:18:38 GMT

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?