cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.  Helper I

## Summarizing - Grouping values in a calculate table

Hi guys,

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(
'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

1 ACCEPTED SOLUTION  Super User

Try

``````Tabella =
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 4  Super User

Try

``````Tabella =
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" )
)
``````  Helper I

hi @johnt75 ,

No works, still same issue  Super User

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

It was working, my mistake. Thank you

ES Announcements #### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features. #### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (5,659)