Forum Discussion
Measure Optimization - Margin Analysis
Hi Anonymous
It took a little bit time to go through your measures. The -3045% came with a little modification on your original ones, all the EstimateTotalCost, you only need to add all the cost, but not iteration (means no SUMX).
Is the Margin% comparing with TotalCost not Revenue?
InHouseMargins = VAR T1 = GENERATE ( GROUPBY ( SalesHistory, SalesHistory[SO] ), VAR EstimateType = [EstimateType] VAR TotalRevenue = [SumTotalRevenue] VAR TotalCost = [EstimatedTotalCost] RETURN ROW ( "EstType", EstimateType, "TotalRevenue", TotalRevenue, "TotalCost", TotalCost ) ) VAR T2 = FILTER ( T1, [EstType] = "In-House" ) RETURN ( SUMX ( T2, [TotalRevenue] ) - SUMX ( T2, [TotalCost] ) ) / SUMX ( T2, [TotalCost] )Basically, I re wrote some of your measures. There should be better ways, but I checked the performance of mine in DAX Studio, all seem ok. PM your email, I can send you the .pbix file.
Hi Anonymous
I am a little bit lost...how to return [InHouseMargin] = -91%. Let's go through some details:
1) your data is to SO level, you might have multiple entries for the same SO, so you need aggregation? Based on sample data you have 1 SO which has 2 entries...let me know if the assumption is incorrect
2) you will use those measures (SUM) in different visuals, like you need to count, to differentiate the type and calculate others?
3) so this InHouseMargin, you need to filter the type = inhouse, then calculate the percentage? I got something far away from -91% with the sample data
Anonymous .
1) your data is to SO level, you might have multiple entries for the same SO, so you need aggregation? Based on sample data you have 1 SO which has 2 entries...let me know if the assumption is incorrect
Yes, need to aggrigate the Revenue and Total Costs to determine margin. Let's not worry about duplicates in this case.
2) you will use those measures (SUM) in different visuals, like you need to count, to differentiate the type and calculate others?
Yes, I use the SUM measures across multiple measures. If possible, can we configure the measures so that we only calculate the table colums (revenue, material cost, labor cost, sub cost, etc) once, and then have the individual measures (in-house margins, sub margins, etc) pull from those calculations. Performance is key to the outcome.
3) so this InHouseMargin, you need to filter the type = inhouse, then calculate the percentage? I got something far away from -91% with the sample data
What you described is what I was trying to, and failing, to accomplish.
-91% is not correct (not sure where I got that number from...) The above outcome of -3045% is correct.
My biggest concern is when I pull in all of our data lines and start running these measures that there are too many calculations required, therefore bogging down the tool and making it useless. I've requested my IT teams drop a calculated column for EstimateType, but have not recieved a timeline for that. This is my next best option (I think).
Thanks again for all your help!
Hi Anonymous
It took a little bit time to go through your measures. The -3045% came with a little modification on your original ones, all the EstimateTotalCost, you only need to add all the cost, but not iteration (means no SUMX).
Is the Margin% comparing with TotalCost not Revenue?
InHouseMargins = VAR T1 = GENERATE ( GROUPBY ( SalesHistory, SalesHistory[SO] ), VAR EstimateType = [EstimateType] VAR TotalRevenue = [SumTotalRevenue] VAR TotalCost = [EstimatedTotalCost] RETURN ROW ( "EstType", EstimateType, "TotalRevenue", TotalRevenue, "TotalCost", TotalCost ) ) VAR T2 = FILTER ( T1, [EstType] = "In-House" ) RETURN ( SUMX ( T2, [TotalRevenue] ) - SUMX ( T2, [TotalCost] ) ) / SUMX ( T2, [TotalCost] )Basically, I re wrote some of your measures. There should be better ways, but I checked the performance of mine in DAX Studio, all seem ok. PM your email, I can send you the .pbix file.