Forum Discussion
Solved
Summarizing - Grouping values in a calculate table
Hi guys,
Please need your help in this topic.
I have a star schema with classic sales/cost/volume values as a fact table and some other dimension table with product, time, country, ecc.
I have created starting from this dataset a table with the below formula:
Tabella =
SUMMARIZE(
ADDCOLUMNS(
'Fact Table',
"FY2022 Revenue", CALCULATE( [Revenue], Dim_Date[Fiscal Year] = "FY2022"),
"FY2023 Revenue", CALCULATE( [Revenue], Dim_Date[Fiscal Year] = "FY2023"),
"FY2022 Quantity", CALCULATE( [Volume], Dim_Date[Fiscal Year] = "FY2022" ),
"FY2023 Quantity", CALCULATE( [Volume], Dim_Date[Fiscal Year] = "FY2023" ),
"FY2022 COGS", CALCULATE( SUM( ICMI[COGS] ), Dim_Date[Fiscal Year] = "FY2022" ),
"FY2023 COGS", CALCULATE( SUM( ICMI[COGS] ), Dim_Date[Fiscal Year] = "FY2023" )
),
Dim_Date[FP Month Number],
Dim_Country[Country],
FactTable[Customer N],
FactTable[Customer],
FactTable[P&L Line ],
FactTable[Material N],
Dim_Material[Material Desc],
[FY2022 Revenue],
[FY2023 Revenue],
[FY2022 Quantity],
[FY2023 Quantity],
[FY2022 COGS],
[FY2023 COGS]
)
The above formula retrieve the table I want but with only a problem: the value are not roll up (I dont know which terms best describe the problem) but I have made and excel file in order to show the desired result.
The above formula retrieve the table I want but with only a problem: the value are not roll up (I dont know which terms best describe the problem) but I have made and excel file in order to show the desired result.
Could someone help me to fix the above dax formula?
Thank much for your precious help
Try
Tabella = ADDCOLUMNS ( SUMMARIZE ( 'Fact Table', Dim_Date[FP Month Number], Dim_Country[Country], FactTable[Customer N], FactTable[Customer], FactTable[P&L Line ], FactTable[Material N], Dim_Material[Material Desc] ), "FY2022 Revenue", CALCULATE ( [Revenue], Dim_Date[Fiscal Year] = "FY2022" ), "FY2023 Revenue", CALCULATE ( [Revenue], Dim_Date[Fiscal Year] = "FY2023" ), "FY2022 Quantity", CALCULATE ( [Volume], Dim_Date[Fiscal Year] = "FY2022" ), "FY2023 Quantity", CALCULATE ( [Volume], Dim_Date[Fiscal Year] = "FY2023" ), "FY2022 COGS", CALCULATE ( SUM ( ICMI[COGS] ), Dim_Date[Fiscal Year] = "FY2022" ), "FY2023 COGS", CALCULATE ( SUM ( ICMI[COGS] ), Dim_Date[Fiscal Year] = "FY2023" ) )
4 Replies
Try
Tabella = ADDCOLUMNS ( SUMMARIZE ( 'Fact Table', Dim_Date[FP Month Number], Dim_Country[Country], FactTable[Customer N], FactTable[Customer], FactTable[P&L Line ], FactTable[Material N], Dim_Material[Material Desc] ), "FY2022 Revenue", CALCULATE ( [Revenue], Dim_Date[Fiscal Year] = "FY2022" ), "FY2023 Revenue", CALCULATE ( [Revenue], Dim_Date[Fiscal Year] = "FY2023" ), "FY2022 Quantity", CALCULATE ( [Volume], Dim_Date[Fiscal Year] = "FY2022" ), "FY2023 Quantity", CALCULATE ( [Volume], Dim_Date[Fiscal Year] = "FY2023" ), "FY2022 COGS", CALCULATE ( SUM ( ICMI[COGS] ), Dim_Date[Fiscal Year] = "FY2022" ), "FY2023 COGS", CALCULATE ( SUM ( ICMI[COGS] ), Dim_Date[Fiscal Year] = "FY2023" ) )
