Summarizing - Grouping values in a calculate table

[emarome94]

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.

Could someone help me to fix the above dax formula?

Thank much for your precious help

[johnt75 · Accepted solution]

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" )
)

[emarome94]

hi @johnt75 ,

No works, still same issue

[johnt75]

can you share a screenshot of the dataview for my code please, I want to see what the raw table looks like.

[emarome94]

It was working, my mistake. Thank you

ES