Grouping Values by Multiple Criteria in several Tables (DAX or Power Query and big Flat Table)

[asjones]

I have don’t this in the past with data from our ERP with Power Query when it was one big flat table. However, I am hitting a big wall trying to do this in Power BI with many related tables.

I need to create some groupings in Power BI for tables and charts. I have looked at several examples, but most seem to have a less complicated data model. I had considered Power Query, but I need to kook at multiple related tables and did not want a massive flat table again. The DAX I saw was confusing and there seemed to be some cautions. I have 5-7 different groupings (A, B, C, D, E…). I have actual expense and budget tables. In order for an amount to be summed into one of the categories it must meet several criteria that are in different related tables.

It must

Be in a certain Account Rage

Be in certain Cost Centers,

Be a specific Project Type

Output might look like this

Category	2025 Actuals YTD	2025 Projection	2026 Budget	2027 Budget	2028 Budget
A	14143	14134	326	3453	433
B	2356	354	3533	5353	3452
C	3423	3497	3534	4345	3455
D	2324	2345	3533	3535	5352
E	2567	2599	5632	3567	3572

I can create a single measure to do one category but, I am really after either a “real” table via Power Query or a DAX query.  Below is an example a measure if I were creating category A as a single measure for Actuals.

Help ang guidance from multiple angles would be appreciated.

Thanks

Alan

For example, for Category A measure might look like this, unfortunately I need that as a “row” not a new column.

A =

VAR _Amount =

    CALCULATE (

        SUM ( 'Actuals'[Amount4] ),

        KEEPFILTERS (

            VALUE ( 'Account Relations'[Head Account] ) >= 50000

                && VALUE ( 'Account Relations'[Head Account] ) <= 69999

                && VALUE ( 'Account Relations'[Head Account] ) <> 56950

        ),

        KEEPFILTERS (

            VALUE ( 'Account Relations'[Account] ) <> 96910

                && VALUE ( 'Account Relations'[Account] ) <> 96950

        ),

        KEEPFILTERS (

            VALUE ( 'Main Project Master File - PMF'[PMF Project Type] ) >= 30

                && VALUE ( 'Main Project Master File - PMF'[PMF Project Type] ) <= 69

        ),

        KEEPFILTERS(

            VALUE('Department Relations'[Cost Center]) >=10

            && VALUE( 'Department Relations'[Cost Center]) <= 20

            )

    )

RETURN

    _Amount

[v-priyankata]

Hi @asjones 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@jgeddes Thanks for your inputs

I hope the information provided by user was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @asjones 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[jgeddes]

Without seeing your exact model it is hard to give an exact answer, however here is one approach you may be able to use.
Using the example model below...

Sample data in the fact table...

You can create a table with the following code...

Table = 
var _catA = 
FILTER(
    fact_table,
    RELATED(dim_parent[Parent Account]) IN {"ABC", "DEF"} && RELATED(dim_child[Child Account]) = 123 && RELATED(dim_project[Project Type]) = 30
)
var _catB = 
FILTER(
    fact_table,
    RELATED(dim_parent[Parent Account]) IN {"DEF"} && RELATED(dim_child[Child Account]) = 456 && RELATED(dim_project[Project Type]) = 30
)
RETURN
UNION(
    SELECTCOLUMNS({"A"}, "Category", "A", "2025 Actuals", SUMX(_catA, [Actual]), "2026 Budget", SUMX(_catA, [2026 Budget]), "2027 Budget", SUMX(_catA, [2027 Budget])), 
    SELECTCOLUMNS({"B"}, "Category", "B", "2025 Actuals", SUMX(_catB, [Actual]), "2026 Budget", SUMX(_catB, [2026 Budget]), "2027 Budget", SUMX(_catB, [2027 Budget]))
)

And end up with a table that looks like this...

Hope this points you in the right direction.

[asjones]

@jgeddes 

Sorry for the delay in responding. Had some issues come up. I really like your idea. That looks clean. I did see many ideas sugges a switch statment and calcualted columns. Even the SQL BI people seemed to go down the SWITCH() and calculcated columns path.
https://www.daxpatterns.com/abc-classification/

Do you have any thoughts on the pros/cons?

[v-priyankata]

Hi @asjones 

The SWITCH() and calculated column method works great when all your grouping rules are in one table. It’s simple to build, performs well, and the category you create can be used right away in slicers or visuals. But once your logic starts pulling from several related tables, it gets harder to manage, you end up needing multiple RELATED() calls, and the model can get heavier. In those situations, creating a summarized table with FILTER and UNION is usually a better choice. It handles multi-table logic more cleanly and keeps your model organized, though it’s static and won’t update with slicers until you refresh the data.

Thanks.