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

[fo6168]

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

[v-bmanikante]

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

[v-bmanikante]

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

[v-bmanikante]

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

[v-bmanikante]

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

[lbendlin · Accepted solution]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[fo6168]

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?

[lbendlin]

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