Measure Optimization - Margin Analysis

[Anonymous]

@Vera_33 I've got another one :-).

I need help with multiple cuts of margin and those cuts % of the total revenue.

In essence, we have three types of bids:

1. In-house

2. Subcontracted

3. Combination

Across 6 measures I need to know the margins and percentage of total revenue that each of these buckets make up.

I was able to sort out the logic on EstimateType which gives me the 3 flags.  I'm now having a hard time trying to find the best way to slice and dice these into the previously mentioned 6 measures.

I've created three samples of what I'm looking for.  They are InHouseMargin, InHouseRevenue, and InHouseBook.  InHouseRevenue and InhouseBook are returning the expected outcomes.  InHouseMargin is not.  It should return a value of -91%.

Feel free to suggest a better path to these.  Seems to me there are many redundant Summing calcs occuring across what will be the 6 measures.

PBIX File - https://drive.google.com/file/d/1yxISUan6KIdk6nVKMUuKQTSGSLo80IYF/view?usp=sharing
Sample Data - https://drive.google.com/file/d/1Uu53QZFkrziBEdoLzORoY__dWnFJlnsj/view?usp=sharing

Thanks in advance!

[Anonymous]

Hi @Anonymous,

Did Vera_33 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestion to help others who faced similar requirements to find it more quickly.

If these also not help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Vera_33]

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]

@Vera_33 .  

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!

[Vera_33 · Accepted solution]

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.