cancel
Showing results for
Did you mean:
Super User

## How can I use summary level ratios to calculate detailed values

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: How can I use summary level ratios to calculate detailed values

@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.

2 REPLIES 2
Community Support Team

## Re: How can I use summary level ratios to calculate detailed values

You may try the measure below.

```Measure =
[Revenue] * CALCULATE ( [Cost % of Revenue], ALLSELECTED ( Sales[Customer] ) )
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User

## Re: How can I use summary level ratios to calculate detailed values

@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.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,583)