Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Ingan
Regular Visitor

Create calculated table from multiple tables with sum and group by DAX

Hi,

I have a problem that i´ve been struggling with.

I want to create a preaggregated calculated table from 1 fact table and 2 dimensions.

 

Fact

FactKeyAccountAmountCostCenterId
1351001022334
23400020066789
39322030066789
49322015066789

 

DimReport

ReportReportRowAccount
Rapport129129/* CM35100
Rapport129129/* CM34000
Rapport129129/* CM93220

 

DimCostCenter

CostCenterIdCostCenterName
22334xy
66789op

 

My desired output in calculated table is this:

 

Ingan_0-1718631845086.png

 

 

 

 

I have tried with ADDCOLUMNS and SUMMARIZE but i only manage to get columns from one dimension and sum of fact columns. I want to have columns from 2 dimensions( Report and CostCenter) and sum of fact columns.

1 ACCEPTED SOLUTION

also try this:

Table =

    SUMMARIZE(
        Fact,
        DimReport[Report],
        DimReport[Account],
        DimCostCenter[CostCenterName],
        "SumAmount", SUM(Fact[Amount])
    )

View solution in original post

3 REPLIES 3
Tutu_in_YYC
Resident Rockstar
Resident Rockstar

You will probably get more replies in you post this in the DAX section.
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands

Anyway, if you can access Power Query editor, using the function Group By to aggregate, and Merge to add those columns from dimension, will be easier.

Thank you so much for your reply! Yes i realize im in the wrong section 🙂 Im working with a tabular model in Visual studio so i can´t access the Power Query editor.

also try this:

Table =

    SUMMARIZE(
        Fact,
        DimReport[Report],
        DimReport[Account],
        DimCostCenter[CostCenterName],
        "SumAmount", SUM(Fact[Amount])
    )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.