states making up top 80% of sales

[sreachard]

I have a request for a report listing the states making up the top 80% of sales, I'm stumped about how to go about this in powerpivot.

My thoughts are I would need a total sales measure, a running total measure and an 80% of total sales measure, states would need ordered by total sales desc, when the running total exceedes the 80% measure you've got your result set.

The logic sounds easy enough when broken down, but i'm stumped.


Thanks for any help

 

[pjmorris]

Are you sure you need a running total? You need a clear definition of what is meant by 'top 80% of sales': this month, this year, ever, for a particular project/component etc etc Once you define exactly what you need to achieve and what data you have available the answer will probably be much clearer. It reminds me of an old Chinese (i think) saying: Seek not to know the answer, but to understand the question!

 

[sreachard]

Are you sure you need a running total? You need a clear definition of what is meant by 'top 80% of sales': this month, this year, ever, for a particular project/component etc etc Once you define exactly what you need to achieve and what data you have available the answer will probably be much clearer. It reminds me of an old Chinese (i think) saying: Seek not to know the answer, but to understand the question!

No, i'm not sure i need a running total, was just thinking out loud about how to get the result. some more detail on what is needed, active insurance policies total premium for a specific month end date, states that make up 80% of total premium.

Thanks

 

[RoryA]

That's standard pivot table functionality. Simply filter the state field for the top 80% by values.

 

[sreachard]

well, i feel kind of dumb and a bit smarter at the same time! Thanks that works. I can add a new sheet with a pivot and connect the slicers and that is working, now my next issue is how do i get the summary data on the first sheet.

there are slicers to select the year and month end date.

business states accounts premium states with 80%of premium
bus1 22 500 3,268,515 16
bus2 18 300 2,459,371 12

the states with the 80% column needs to come from the second sheet I tried converting the second sheet to cube formuals, but i don't think that will work because the number of rows would need to be dynamic.

Thanks

 

[sreachard]

well, i feel kind of dumb and a bit smarter at the same time! Thanks that works. I can add a new sheet with a pivot and connect the slicers and that is working, now my next issue is how do i get the summary data on the first sheet.

there are slicers to select the year and month end date.

business states accounts premium states with 80%of premium
bus1 22 500 3,268,515 16
bus2 18 300 2,459,371 12

the states with the 80% column needs to come from the second sheet I tried converting the second sheet to cube formuals, but i don't think that will work because the number of rows would need to be dynamic.

Thanks