98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Margin % calculation on running total in pivot table
Margin % calculation on running total in pivot table
Resolved · High Priority · Version 2013
Wed 4 Jul 2018, 17:54Delegate Tsudoi said...
Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course
Margin % calculation on running total in pivot table
Hi there,
I have a question about how to calculate margin % on running total in power pivot table.
So I have the table as follows.
Month (1 to 12) in column A
YTD Budget Sales in column B
YTD Budget Margin in column C
YTD Actual Sales in column D
YTD Actual Margin in column E
YTD Budget Margin % in column F
YTD Actual Margin % in column G
Column B to E are shown as running total in Month and calculated in DAX like the following (Budget)
For Sales = CALCULATE(SUM(Shipped[Sales]),DATESYTD(Shipped[Ship Date]),Shipped[Type]="Budget v2")/1000
For Margin = CALCULATE(SUM(Shipped[Margin]),DATESYTD(Shipped[Ship Date]),Shipped[Type]="Budget v2")/1000
The problem is, when I try to calculate margin %, it calculates and shows result per month instead of running total basis.
The calculation is simply, Margin Measure / Sales Measure but I want to see margin % in running total.
I think this happens whether powerpivot or normal pivot but I cannot get the result I want..
Please can you advise?
Thanks,
Wed 4 Jul 2018, 23:15Trainer Jens said...
RE: Margin % calculation on running total in pivot table
Hi Tsudoi,
Thank you for the forum question.
Please have a look at my answer to your other question.
Your problems with your DAX measures can be how the data model is build.
If you can calculate it to show result per month, just right click one of the values and click SHOW VALUES AS and click RUNNING TOTAL IN....
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 5 Jul 2018, 07:35Delegate Tsudoi said...
RE: Margin % calculation on running total in pivot table
Hi,
Thanks for your reply
The data model is fine, it's all in one table, no relationship. So problem there. And as I said initially, Sales and margin values are shown as running total in Month. The problem is I am explaining is with margin %.
If I do the same running total in Month for %, it just sums percentages which is wrong.
So I want to see each month % but in running total. It currently calculates % in single month for each month. This issue is not because of any Dax because it also happens to normal pivot.
I hope it makes sense?
Regards
Thu 5 Jul 2018, 10:04Trainer Jens said...
RE: Margin % calculation on running total in pivot table
Hi Tsudoi,
Yes running total will sum up. What want you it to do?
If you want the percentage different from previous month you can right click the monthly percentage and click Show values as and then click % Different from and select previous.
A data model without relationships is not a data model. If you just use PowerPivot to be able to work with a huge data set you will give yourself a lot of unnecessary hard work.
I would use PowerQuery and just make a connection to the source.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 5 Jul 2018, 11:07Delegate Tsudoi said...
RE: Margin % calculation on running total in pivot table
Hi,
I guess my question is simpler than how I am sounding.
So, disregard the fact that I am using power pivot because this happens to normal pivot as well.
So, what I want to see is margin % correctly calculated against Sales/Margin shown as Running Total in Month.
At the moment, margin % is calculated for each month, not YTD based.
The principle solution should be the same for powerpivot and no powerquery needed I imagine but at the moment, I cannot figure out how to do this simple thing..
Regards
Thu 5 Jul 2018, 12:05Trainer Jens said...
RE: Margin % calculation on running total in pivot table
Hi Tsudoi,
Please find my attached file. I hope that this is what you want.
If you cannot find the attachment wait 10 minutes and refresh your browser. The attachment is placed under the signature.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Tsudoi.xlsxThu 5 Jul 2018, 14:26Delegate Tsudoi said...
RE: Margin % calculation on running total in pivot table
Thanks for the attachment.
However, you seem to have misunderstood what I am trying to explain here.. Sorry I did not explain well.
I am not trying to show running total of margin %.
I am trying to show margin % in each month for accumulated margin in value each month. Does it make sense?
So if I create another pivot using the data you sent with running total of ActualSales and ActualMargin and do Calculated Field to calculate margin %, it returns % for each month, not on accumulated basis. That's is the problem I am trying to explain..
I hope it's clearer..?
Thanks,
(I can send back the file to you with what I mean if needed)
Thu 5 Jul 2018, 14:30Trainer Jens said...
RE: Margin % calculation on running total in pivot table
Hi Tsudoi,
Please send it to info@microsofttraining.net
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 5 Jul 2018, 15:01Trainer Jens said...
RE: Margin % calculation on running total in pivot table
Hi Tsudoi,
Please find attached.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Thu 5 Jul 2018, 15:13Delegate Tsudoi said...
RE: Margin % calculation on running total in pivot table
Hi Jens,
I am not really asking for how to use Calculated Field...
I am using it but it does not return the results I want. That's why I am asking you for help.
As I included in the file, the pivot does not calculate the same results I showed next to it.
The small table that says Budget and Actual shows margin % the pivot table should calculate and show, which is based on running total, not individual month.
Regards
Tsudoi
Thu 5 Jul 2018, 16:45Trainer Jens said...
RE: Margin % calculation on running total in pivot table
Hi Tsudoi,
I am sorry but calculated fields are not a solution as you pointed out.
I am also sorry because I have not been able to find a solution to your issue. I have tested a lot of different ideas and I have tried to find a solution googling the problem, but I didn't find a solution.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 5 Jul 2018, 18:08Delegate Tsudoi said...
RE: Margin % calculation on running total in pivot table
Thanks for trying Jens.
As I am sure you are not the only instructor there at STL, has anyone else seen the problem before and knows a solution...?
As I said, this is not Dax related issue and so seems rather basic pivot table functional issue?
Regards
Thu 5 Jul 2018, 18:31Trainer Jens said...
RE: Margin % calculation on running total in pivot table
Hi Tsudoi,
You will not find a PivotTable solution. You will have to look for a DAX solution or a VBA solution.
PivotTables have many limitations.
We can find a DAX expert, which can code the DAX for you or we can VBA code it but there will be cost involved.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 5 Jul 2018, 18:46Delegate Tsudoi said...
RE: Margin % calculation on running total in pivot table
Hi,
This is simple to resolve I am sure but what sort of cost are you talking about?
Regards
Thu 5 Jul 2018, 18:59Trainer Jens said...
RE: Margin % calculation on running total in pivot table
Hi Tsudoi,
I will ask one from the sales team to contact you.
There are different DAX expert websites. PowerPivotPro is one of them I use.
You are may be right, that this is simple to resolve, but I have not been able to do it or been able to find a solution.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 12 Jul 2018: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Moving between split pane sections in a spreadsheetIf you have used the split panes feature in your worksheet, use the following keyboard shortcut keys to move quickly and effortlessly between paned sections: |
