Parameter for a dinamic table?

[greta]

Goodmorning everyone, 

I have a table where, for each project, to different cost types to which are associated different categories (and a total value), as an example, for a project:
Cost Type | Categories | Value

ct1            | cat 1          | 2.0

ct2            | cat 2          | 3.2

ct 2           | cat 1          | 6.4

ct 2           | cat 3          | 6.1

ct 1           | cat 4          | 0

I'd need to build a dynamic table, based on the filter context (single or multiple projects) that identifies the categories listed in different cost types and aggregates in a new "total" category the others. So the above example would become

Cost Type | Categories | Value

ct1            | cat 1          | 2.0
ct 2           | cat 1          | 6.4

ct 2           | tot             | 3.2+6.1

ct 1           | cat 4          | 0

I've tried to create a parameter but it kees n returning me the sme categories with no aggregation, What am I missing? is there another way to achieve the result? Many thanks for your time

[rohit1991 · Accepted solution]

Hi @greta ,

To achieve a dynamic table in Power BI that aggregates categories into a "tot" group when they are not shared across multiple cost types, parameters alone won't work because they are static and don't respond to filter context. Instead, you'll need to use DAX to create a calculated table that evaluates which categories appear in more than one cost type and preserves them, while aggregating all others under a new "tot" category.

This approach ensures your output adapts dynamically to slicers or filters such as project selection, and correctly groups and summarizes your data for reporting purposes.

Here’s a DAX example for the calculated table:

DynamicTable =
VAR SharedCategories =
    CALCULATETABLE (
        VALUES ( 'YourTable'[Categories] ),
        FILTER (
            'YourTable',
            CALCULATE ( DISTINCTCOUNT ( 'YourTable'[Cost Type] ) ) > 1
        )
    )
RETURN
    SUMMARIZE (
        ADDCOLUMNS (
            'YourTable',
            "NewCategory",
                IF (
                    'YourTable'[Categories] IN SharedCategories,
                    'YourTable'[Categories],
                    "tot"
                )
        ),
        'YourTable'[Cost Type],
        [NewCategory],
        "Value", SUM ( 'YourTable'[Value] )
    )

[rajendraongole1]

Hi @greta  -please find the attached pbix file that i have implement a solution that works dynamically with filters like project selection.

[greta]

Thank you very much for your time!