• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

calculate percentage based on dynamic denominator

I

Izhar

Member
hello folks,


in the attached sheet there are columns g to j calculate the ops cost as a % of revenue based on month, quarter, year and global.


I want to create a pivot graph with the slicers for month, quarter, year, global, status and country


And based on my selection for the time duration the appropriate revuen and ops cost must be selectded.


Will appreciate any ideas.


Thanks,
Izhar
 

Attachments

  • Dynamic dem sample file.xlsx
    264 KB · Views: 5
If you want to be able to group dates by M, Q, Y you are better off using true excel dates in your records - Excel will handle that for you. Currently, you're grouping by Text Descriptors, being the Year, Quarter Month columns in your data Table. For each record, all you need is the specific transaction date.

Make the data-set into an excel Table and it will provide the Groupings you are currently creating "outside" the Pivot Tables, within the PT. If i'm understanding your process correctly, simplifying your data will allow you to get the OSR% calculation on the fly from within the PT. Currently, you're process is using a calculation from your pivot table to calculate the OSR% in your original data range, and that's a tad confusing. I believe you can dispense with your "intermediate lookup pivot" - it's an unnecessary step. However, I may not be properly understanding your goal

Are you creating this data, or is it linked to an external file? Do you have access to the specific dates for each record in your dataset? I'm sure you do, as you're using a base date to calculate Year, Qtr, Mo.

If you can provide a sample of your original raw data, perhaps i can lay out the process from start to finish?
 
Good morning,

thank you for taking the time to reply

the goal is to automate the denominator so when M Q or Y is selected the appropriate calculation comes uo.

the data is from an external file the M, Q, Y are added by me.

if a data is required use the 1st of every month

feel free to remove the columns h to k to see the orginal data set.

thank you again.
 
If you can provide the recordset with the date as you receive it, prior to any manipulation for Year Month Qtr, it will make it a lot easier to assist.
 
You must log in or register to reply here.
Back
Top