cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Iterate over table rows to calculate group subtotals

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!

1 ACCEPTED SOLUTION
Super User

Hi @CloudHerder ,

you can try this:

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

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

2 REPLIES 2
Super User

Hi @CloudHerder ,

you can try this:

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

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Resolver I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors