Iterate over table rows to calculate group subtotals

Anonymous — Thu, 27 Jul 2023 04:53:02 GMT

This is a bit of an odd post, since I have a workable solution to my task. I just can't help feeling there's a simpler, more elegant way to accomplish the task.

I have a hierarchical table of activities, subactivities, and complexity levels. I need to add a column that computes the percentage weight for each activity sublevel. Here's the table:

Activity	Subactivity	Level	Weight
A	SA01	1
A	SA02	1
A	SA03	2
A	SA04	2
A	SA05	2
A	SA06	3
A	SA07	3
A	SA08	4
B	SA09	1
B	SA10	2
B	SA11	2
B	SA12	3
B	SA13	4
B	SA14	4
C	SA15	1
C	SA16	1
C	SA17	1
C	SA18	1
C	SA19	1
C	SA20	2
C	SA21	2
C	SA22	3
C	SA23	3
C	SA24	3
C	SA25	4
C	SA26	4

In the weight column, I want to compute the percentage contribution of each Subactivity for each combination of Activity and Level. For example, there are two Subactivity values for Activty = A and Level = 1, so these would each have a Weight = 50%.

Here's my current code for the Weight column:

Weight = VAR tmpTable = SUMMARIZE( 'Data', 'Data'[Activity], 'Data'[Level], "Pct", DIVIDE(1, COUNTROWS(DISTINCT('Data'[SubActivity])), BLANK()) ) VAR currentActivity = [Activity] VAR currentLevel = [Level] VAR filteredTable = FILTER(tmpTable, [Activity] = currentActivity && [Level] = currentLevel) VAR result = SUMX(filteredTable, [Pct]) RETURN result

This just feels kludgy. How can I simplify this?

Many thanks!

Re: Iterate over table rows to calculate group subtotals

ERD — Thu, 27 Jul 2023 06:36:41 GMT

Hi Anonymous ,

you can try this:

Weight = 1 / CALCULATE(DISTINCTCOUNT(Table_[Subactivity]), ALLEXCEPT(Table_,Table_[Activity], Table_[Level]))

Re: Iterate over table rows to calculate group subtotals

Anonymous — Thu, 27 Jul 2023 17:41:28 GMT

ERD Thanks so much! This is just what I was looking for. I need to get more familiar/comfortable with the ALLEXCEPT function.

topic Iterate over table rows to calculate group subtotals in DAX Commands and Tips

Iterate over table rows to calculate group subtotals

Re: Iterate over table rows to calculate group subtotals

Re: Iterate over table rows to calculate group subtotals