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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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