Other options for calculating margin categories by product and Year-Month
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
