cancel
Showing results 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

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

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

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors