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

[Ingan]

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

FactKey	Account	Amount	CostCenterId
1	35100	10	22334
2	34000	200	66789
3	93220	300	66789
4	93220	150	66789

DimReport

Report	ReportRow	Account
Rapport129	129/* CM	35100
Rapport129	129/* CM	34000
Rapport129	129/* CM	93220

DimCostCenter

CostCenterId	CostCenterName
22334	xy
66789	op

My desired output in calculated table is this:

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.

[Tutu_in_YYC]

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.

[Ingan]

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.

[Tutu_in_YYC · Accepted solution]

also try this:

Table =

    SUMMARIZE(

        Fact,

        DimReport[Report],
        DimReport[Account],

        DimCostCenter[CostCenterName],

        "SumAmount", SUM(Fact[Amount])

    )