How can I use summary level ratios to calculate detailed values

[jdbuchanan71]

The company is question has two lines of business

• Bikes
• Accessories

The revenue and cost for Accessories are always added to the sales table at the same level of detail so there is no problem there.  For Bikes, the revenue is added at a more granular level than the cost and at different times.  Revenue is posted more often and includes the customer level where cost is posted twice a month and does not include the customer.

Given that the cost is in the same month as the revenue and posted to the same division (East, West) I am trying to figure out how to use the summary level 'Cost % of Revenue' and apply that to the detailed revenue amount so I get a detailed cost amount.

I was able to calculate a summary table that shows the [StartOfMonth] [Division] [Bike Cost % of Revenue] but I couldn't get it to calculate the final amount and I'm not sure that was even the right way to go about it.

I have uploaded a pbix file with the model so far and since a picture is worth a 1000 words.

Any help would be greatly appreciated.

Thank you

[v-chuncz-msft]

@jdbuchanan71,

You may try the measure below.

Measure =
[Revenue] * CALCULATE ( [Cost % of Revenue], ALLSELECTED ( Sales[Customer] ) )

[jdbuchanan71 · Accepted solution]

@v-chuncz-msft

Thank you for the suggestion!  Based on that I believe I was able to get to a solution.  If I used the measure as is the calc would be off when I included Accessories and I also needed to include more than just the customer in the ALLSELECTED.

First, a measure to calc just Cost % of Revenue for Bikes.

Cost % of Revenue - Bikes =
CALCULATE (
 [Cost % of Revenue],
 FILTER ( GLAccounts, GLAccounts[LOB] = "Bikes" ),
 ALL ( Customers ),
 ALL ( Reps )
)

Then a measure to apply the % to the Bike Revenue

Bike Cost 2 =
[Bikes Revenue]
    * CALCULATE (
        [Cost % of Revenue - Bikes],
        ALLSELECTED ( Customers ),
        ALLSELECTED ( Reps )
    )

I have updated the PBIX file if anyone is interesed in the model.