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

How to Calculate a % from a measure of a Column Total not the Row Value in Power Pivot

Justmeok

New Member
Hi Everyone :)
I am not very skilled with Power Pivot and I have a pivot table that has a column total for annual budget broken up for different sales reps. The % amount is achieved by using a measure. I am trying to get a measure that replicates the excel formula I have in the attached file. You will see my attempt at getting the measure to work. Failure No 1 (well to be honest I've lost count of the different measures I've tried but suffice to say they have all failed :eek:)
Looking forward to some help please? :DD
 

Attachments

Chihiro

Excel Ninja
It's bit hard to help with DAX without representative data model or at least the raw data table(s).

DAX construction will largely depend on how your model is structured.
 

Justmeok

New Member
Ok thank you I will try to replicate the problem with some sample data and upload the file again
 

GraH - Guido

Well-Known Member
Justmeok, I think you are after this, see attached.

Looking at your sample and the way you question SUM of FY19, I'm concluding you do not know the basics of DAX syntax yet. Simply doing a SUM on a column never returns the grand total. It returns the SUM of the values used in the pivots' coordinates (row/column). So in your case it will return the sum of sales for SalesRep 1, SalesRep2 , ... and so on. If you'd have multiple lines with SalesRep1 then only those would be summed up.

To make the pivot you want I made the following measures:
SumOfActual: =sum([Actual FY19])
GrandTotalOfActual: =CALCULATE ( [SumOfActual] , ALL (tSales ) )
%OfTotalActuals:= [SumOfActual]/[GrandTotalOfActual]
%AppliedOnTotalBudget:=Sum(tSales[Budget FY20])*[%OfTotalActuals]

Before switching to DAX/PowerPivot I kindly suggest to follow a course or buy some books on the topic.
 

Attachments

Justmeok

New Member
Hello there GraH - Guido :)
Thank you so much again for helping me!
It still needs a little tweak as per my attached file - of course I have tried to create new measures based on your lovely clear explanation above but I'm sadly not up to the task :confused: Could you kindly have another look and point me in the correct direction please?
BTW I will definitely take your advice and do a course on power pivot - it seems quite complicated!
Thanks again
 

Attachments

geniusmined

New Member
Hi Please I need your help in translating already calculated percentages unto a pivot table? I know the default on how to have the pivot make a percentages in relation to the other rows but in this case the rows (specific) locations already have a specific percentage and I would need to collate such percentages to make sense of the data.

Please see a sample set as attached.
 

Attachments

Justmeok

New Member
Hi:

Is this what you are looking for?

Thanks
Hi Nebu :)

This is exactly what I am looking for! This is such a great solution and so easy now I've seen what you have done.

Thank you so much for helping me, it's really appreciated :)

This site has so many awesome helpers!!

Thanks again.
 

GraH - Guido

Well-Known Member
Hello there GraH - Guido :)
Thank you so much again for helping me!
It still needs a little tweak as per my attached file - of course I have tried to create new measures based on your lovely clear explanation above but I'm sadly not up to the task :confused: Could you kindly have another look and point me in the correct direction please?
BTW I will definitely take your advice and do a course on power pivot - it seems quite complicated!
Thanks again
Hi,
1. You're welcome :)
2. It was just a matter of correctly understanding what you were after. Only a little tweak was required.
3. Nebu's solution is a calculated column. That's fine, though it might have an impact on performance. Calculated columns get stored in-memory (and when using a 32-bit version of Excel with larger datasets, that's trouble guaranteed), whereas measures are calculated on the fly (need CPU power to calculate faster). It sounds like a trade-off? I've noticed DAX-experts almost always prefer measures. As Rob Collie sees it, if a measure is not returning a result under 3s, you have not written the optimal measure. Just so you know, a measure is calculated for each intersect (cell) of the pivots' row/column coordinates. In your example each measure is calculated 7 times.

Complicated? Yes and no, it depends on the solutions you need to build. If they are a bit like the example, I believe one can learn the correct basics of PowerPivot and DAX rather quickly and already travel far. However, having in-depth understanding of data model and DAX will take time callibrating your DAX compass by doing, failing, doing better. Ending up lost in the DAX dessert, is not what you'd want.
 

Attachments

GraH - Guido

Well-Known Member
Hi Please I need your help in translating already calculated percentages unto a pivot table? I know the default on how to have the pivot make a percentages in relation to the other rows but in this case the rows (specific) locations already have a specific percentage and I would need to collate such percentages to make sense of the data.

Please see a sample set as attached.
Please do not hijack a thread of some-else. As it is mentioned in the site rules Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
 

geniusmined

New Member
Please do not hijack a thread of some-else. As it is mentioned in the site rules Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
Hi, I had no intention on doing such and I was unaware of the rules.
 
Top