Forum Discussion

greta's avatar
greta
Frequent Visitor
1 year ago
Solved

Parameter for a dinamic table?

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

Need Help
Tips and Tricks
Tutorial Requests
  • rohit1991's avatar
    rohit1991
    1 year ago

    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] )
    )



     

3 Replies

    • greta's avatar
      greta
      Frequent Visitor
      1 year ago

      Thank you very much for your time!

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    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] )
    )