Forum Discussion
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
| 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.
also try this:
Table =SUMMARIZE(Fact,DimReport[Report],
DimReport[Account],DimCostCenter[CostCenterName],"SumAmount", SUM(Fact[Amount]))
3 Replies
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.also try this:
Table =SUMMARIZE(Fact,DimReport[Report],
DimReport[Account],DimCostCenter[CostCenterName],"SumAmount", SUM(Fact[Amount]))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.
