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

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

Reply
tracytran91
Helper III
Helper III

Summarize table

Hi friends, 

 

I want to create a calculated table using Summarize function which contains "Sales" and "Cost" measure. 

 

Sales = CALCULATE(SUM(F_Sales[NetSales]), 
TREATAS(VALUES('D_Table'[year_month]),F_Sales[yearmonth]), 
TREATAS(VALUES('D_Table'[customer_no]),F_Sales[customer_no]))
Cost = CALCULATE(SUM(F_Cost[Cost]),
                        TREATAS(VALUES('D_Table'[sup_code]), F_Cost[sup_code]),
                        TREATAS(VALUES('D_Table'[year_month]),F_Cost[year_month]), 
                        TREATAS(VALUES('D_Table'[Product]),F_Cost[Product]))

 

Here is my calculated table:

Summarize_table = 
SUMMARIZE(D_Table,
                            D_Table[rep_code],
                            D_Table[sup_code],
                            D_Table[year_month],
                            D_Table[product],
                            D_Table[Region],
                            "Sales",[Sales]
                            ,"Cost",[Cost]
                            )

 

I compare the Sales measure and Cost measure in original table with Sales and Cost column in Summazie_table. The Sales gave me correct number. However, the cost made alot of difference. 

 

I had no idea why I used exactly the same measures Sale and Cost in summarize_table. However, the result of cost is different. I want it exactly the same. 

 

tracytran91_0-1636719330712.png

 

Could anyone please help me to solve it out?

Thank for your help. 

 

1 ACCEPTED SOLUTION
tracytran91
Helper III
Helper III

I realized that my sale and cost measure using TREATAS must use the same granularity to make it work. 

View solution in original post

4 REPLIES 4
tracytran91
Helper III
Helper III

I realized that my sale and cost measure using TREATAS must use the same granularity to make it work. 

Anonymous
Not applicable

Hello @tracytran91 

I hope below code works for you.

Summarize_table =
VAR SalesTable =
SUMMARIZE(
D_TABLE,
'D_Table'[year_month],
'D_Table'[customer_no],
"Sales",[Sales]
)

VAR CostTable =
SUMMARIZE(
D_TABLE,
'D_Table'[sup_code],
'D_Table'[year_month],
'D_Table'[Product],
"Cost",[Cost]
)

RETURN

UNION(SalesTable,CostTable)

Thank You.

@Anonymous Thank for your reply. 

 

I gave it a try and UNION function make the column Sales and Cost becomes a "sales" column.  It is not my expectation. 

 

tracytran91_0-1636771519147.png

 

Anonymous
Not applicable

Hello @tracytran91 
Could you please share the pbix file after removing the sensitive information?
Thank You.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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