topic Re: How to calculate accumulated percentage in DAX Commands and Tips

How to calculate accumulated percentage

LotteLaugesen — Tue, 20 Feb 2024 08:55:51 GMT

How do you calculate accumulated percentage when time is not involved?
It should accumulate per Logon name.

Best regards
Lotte

Re: How to calculate accumulated percentage

Dangar332 — Tue, 20 Feb 2024 11:59:42 GMT

Hi, LotteLaugesen

if time is not present for comparing purpose then use below method it might work
make a new column

Column 2 = var a = sumx(FILTER(ALL('Table'),'Table'[view]>EARLIER('Table'[view])),'Table'[view]) var b = 'Table'[view]+a var c = SUM('Table'[view]) return b/c

and don't forgot to change column type to percantage

Re: How to calculate accumulated percentage

LotteLaugesen — Tue, 20 Feb 2024 12:24:53 GMT

Hi Dangar332

Thanks for your answer, but 'Report Views' is a measure not a columnn in the table.
The table is not summarized, but has 1 record per view.

Report Views = COUNT(FT_PBI_ActivityLog[ReportName])

Do you have any other ideas?

Re: How to calculate accumulated percentage

v-weiyan1-msft — Wed, 21 Feb 2024 03:12:23 GMT

Hi LotteLaugesen ,

Dangar332 nice method! Thank you, for your quick response and the solution provided.
I am not sure how your semantic model looks like, but based on the sample and description you provided, I tried to create some dummy data.

Please try code as below.

% of Total = VAR Total_View = COUNTROWS(ALL('Table')) RETURN [Report Views] / Total_View

Accum % = VAR Total_View = COUNTROWS(ALL('Table')) RETURN CALCULATE ( DIVIDE ( SUMX(VALUES('Table'[Login name]),[Report Views]), Total_View ), FILTER ( ALL ( 'Table'[Login name] ), 'Table'[Login name] <= MAX ( 'Table'[Login name] ) ) )

Result is as below.

For further details，please find attachment.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to calculate accumulated percentage

LotteLaugesen — Wed, 21 Feb 2024 08:46:26 GMT

Thank you, v-weiyan1-msft

Your calculation is not giving me the correct result, maybe it's because the visual is order by Report views and not Logon name.
But this calulation (almost) works:

Accum % Logons = IF( ISINSCOPE( FT_PBI_ActivityLog[Logon name] ), VAR CurrentRank = [Report View Rank By Logon] VAR RankAndViews = ADDCOLUMNS( ALLSELECTED(FT_PBI_ActivityLog[Logon name]), "@Ranking", [Report View Rank By Logon], "@Views", [Report Views] ) VAR MoreViews = FILTER(RankAndViews, [@Ranking] <= CurrentRank ) VAR Result = SUMX(MoreViews, [@Views]) RETURN DIVIDE(Result,CALCULATE(COUNT(FT_PBI_ActivityLog[ReportName]),REMOVEFILTERS(FT_PBI_ActivityLog[Logon name]))) )

Except when 2 or more Logon name have the same no of Report views

Lotte