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

[Justmeok]

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 )
Looking forward to some help please?

 

[Chihiro]

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]

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

 

[GraH - Guido]

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.

 

[Justmeok]

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

 

[Nebu]

Hi:

Is this what you are looking for?

Thanks

 

[geniusmined]

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.

 

[Justmeok]

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]

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

 

[GraH - Guido]

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]

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.

 

[Justmeok]

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.

Ahhhh GraH - Guido yet again you have solved the issue with another clear explanation

Looking at your solution it just looks so darn easy and to be honest I thought I had tried this option when I was trying to do it myself! Clearly not successfully
I do understand your comments regarding the resources and think this is an excellent solution since the actual data is quite large so a faster calculation time is always good.

As for the DAX desert - you made me laugh - that's definitely not a place I want to visit any time soon

My sincere thanks again for your help, so appreciated