Using CALCULATE to summarize different values

[Jeff2Jets]

I have two tables. The first 'Folders' has the following columns/values:

Folder Name       Items

\Calendar                  2

\Calendar                  3

\Calendar                  4

\Calendar\Birthdays  5

\Calendar\Holidays   6

\Drafts                       7

The second 'Folders Summary' has the following:

Folder Name

\Calendar

\Drafts 

I want to add the column 'Items' to the 'Folders Summary' table and I want a formula (e.g., CALCULATE?) that will create the following values:

\Calendar          20

\Drafts                7

So the formula would sum the values for all rows that are '\Calendar' or start with '\Calendar\'.

Is that possible?

[v-tejrama]

Hi @Jeff2Jets ,

Thanks for reaching out to the Microsoft fabric community forum.

I tested this scenario and successfully achieved the result the OP was looking for. There are two tables: the Folders table, which lists folder names such as \Calendar, \Calendar\Birthdays, \Drafts, etc., along with item counts; and the Folders Summary table, which contains only the top-level folder names like \Calendar and \Drafts.

The goal was to calculate the total item count for each summary folder, including all subfolders that start with that name. For instance, \Calendar should aggregate items from \Calendar, \Calendar\Birthdays, and \Calendar\Holidays.

To accomplish this, I created a measure in the Folders Summary table using the following DAX:

Total Items =
CALCULATE (
    SUM ( 'Folders'[Items] ),
    FILTER (
        ALL ( 'Folders' ),
        LEFT ( 'Folders'[Folder Name], LEN ( SELECTEDVALUE ( 'Folders Summary'[Folder Name] ) ) )
            = SELECTEDVALUE ( 'Folders Summary'[Folder Name] )
    )
)

Please find the attached pbix file for your reference.
Best Regards,
Tejaswi.
Community Support

[v-tejrama]

Hi @Jeff2Jets ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Thank you.

[Sandip_Palit]

1. Go to the 'Folders Summary' Table
In Power BI's Data View, select the 'Folders Summary' table from the Data pane on the right.

2. Create a New Column
From the Table tools ribbon at the top, click New column.

3. Enter the DAX Formula
In the formula bar, enter the following DAX expression. This formula calculates the sum for each folder in your summary table.

Items =
VAR CurrentFolder = 'Folders Summary'[Folder Name]
RETURN
CALCULATE (
SUM ( 'Folders'[Items] ),
FILTER (
'Folders',
'Folders'[Folder Name] = CurrentFolder
|| STARTSWITH ( 'Folders'[Folder Name], CurrentFolder & "\" )
)
)
After you press Enter, the new Items column will be populated with the correct totals.

If this explanation and solution resolve your issue, please like and accept the solution.

[wardy912]

Hi @Jeff2Jets 

Try this:

Items = 
CALCULATE(
    SUM(Folders[Items]),
    FILTER(
        Folders,
        LEFT(Folders[Folder Name], LEN('Folders Summary'[Folder Name])) = 'Folders Summary'[Folder Name]
    )
)

Please give a thumbs up and mark as solved if it helps, thanks!