topic Re: Other options for calculating margin categories by product and Year-Month in DAX Commands and Tips

Other options for calculating margin categories by product and Year-Month

fo6168 — Thu, 10 Apr 2025 22:05:50 GMT

I have a business case to calculate revenue and margin for products within a certain margin range by year and month. For example, product category "A" has $10,000 sales in January 2025. In category A, $4,000 worth of products were under 10% margin.

I started by creating a calculated table so I can calculate margin % by product and year month, then assigning a category. I think this will work for my use case, but I am afraid that the calculated table will reduce performance and if the semantic model is moved to Fabric, we won't be able to use DirectLake mode if a calculated table exists. Is there a way to achieve a similar result using measures?

Below is the expression I used to create the calculated table. I also included a screenshot of the end result I am trying to achieve for reference.

Below the table shows the % of revenue for products below 10% margin :

The below DAX expression is what I am using to create a calculated table:

VAR ProductsByYearMonth =
SUMMARIZE (
'Global Sales Invoicing',
ProductDimExtended[Product_Dim_dKey],
LegalCustomerDim[Customer_Dim_dKey],
Time[YearMonth]

)

VAR MarginByYearMonthProduct =
ADDCOLUMNS(
ProductsByYearMonth,
"@ProdSales",[TotalInvoicedLineValue_USD],
"@ProdCost",[TotalAdjustedCost_USD],
"@ProdMargin",[MarginAdjusted_USD],
"@ProdMargin%",[MArgin%Adjusted_USD],
"@MarginCategory", SWITCH(
TRUE,
[TEST_Margin] <= 0.1, "Low",
[TEST_Margin] <= 0.2, "Medium",
"High"
)
)

VAR Result =
SELECTCOLUMNS (
MarginByYearMonthProduct,
"ProductKey", ProductDimExtended[Product_Dim_dKey],
"CustomerKey", LegalCustomerDim[Customer_Dim_dKey],
"YearMonth", Time[YearMonth],
"ProductSales", [@ProdSales],
"ProdMargin",[@ProdMargin],
"ProdMargin%", [@ProdMargin%],
"MarginCategory", [@MarginCategory]
)
RETURN
Result

Re: Other options for calculating margin categories by product and Year-Month

lbendlin — Thu, 10 Apr 2025 23:38:32 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Other options for calculating margin categories by product and Year-Month

fo6168 — Fri, 11 Apr 2025 15:32:51 GMT

Hi, what type of data would be helpful to share? The transactional data that I am rolling up into the calculated table or the data that I have in the calculated table already?

Re: Other options for calculating margin categories by product and Year-Month

lbendlin — Fri, 11 Apr 2025 19:50:25 GMT

You seem to want to avoid the calculated table - so I guess the underlying data.

Re: Other options for calculating margin categories by product and Year-Month

Anonymous — Thu, 24 Apr 2025 12:47:36 GMT

Hi fo6168 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

B Manikanteswara Reddy

Re: Other options for calculating margin categories by product and Year-Month

Anonymous — Mon, 28 Apr 2025 14:50:26 GMT

Hi fo6168 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

B Manikanteswara Reddy

Re: Other options for calculating margin categories by product and Year-Month

Anonymous — Thu, 01 May 2025 15:22:46 GMT

Hi fo6168 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

B Manikanteswara Reddy

Re: Other options for calculating margin categories by product and Year-Month

Anonymous — Tue, 03 Jun 2025 19:09:06 GMT

Hi fo6168 ,

As we haven’t heard back from you, we will go ahead and close this ticket for now.

If you need any further assistance, please don’t hesitate to raise a new ticket, we’re always happy to help.

Our sincere apologies if there was any inconvenience caused.

Regards,

B Manikanteswara Reddy