Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

Reply
emarome94
Helper I
Helper I

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.
 
emarome94_0-1682000275183.png

 

 
 
Could someone help me to fix the above dax formula?
 
Thank much for your precious help
 
 
1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

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

hi @johnt75 ,

 

No works, still same issue

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

It was working, my mistake. Thank you

 

ES

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.