cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
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.

 

CostRatioExample.jpg

Any help would be greatly appreciated.

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
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.

 

 

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

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

@jdbuchanan71,

 

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

 

 

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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