% per row record based on a total which is filtered

[Sonova]

Hi

Please could someone assist in pointing me in the right direction.

I have a table which lists cost code categories and what I am trying to do is obtain the percentage of each cost code based on the total prime cost. I needed to aggregate the prime cost based on a filter as the table includes revenue and other costs which need to be excluded.

My formula works for each row but the percentage is not correct.

Forecast % =calculate(sum([Abs Forecast]))/calculate(sum([Abs Forecast]),filter(Projectj56,Projectj56[Prime Cost Filter]=1)))

[TABLE="width: 500"]
<tbody>[TR]
[TD]Prime Cost filter[/TD]
[TD]Abs Forecast[/TD]
[TD]Forecast %[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]290 308[/TD]
[TD]10%[/TD]
[/TR]
</tbody>[/TABLE]

I created a measure which has the same properties as my denominator above to cross check.

Forecast Prime Cost:=calculate(sum([Abs Forecast]),filter(Projectj56,[Prime Cost Filter]=1))

The total is 3 767 790. The correct percentage is 290 308/3 767 790 =7%. However my formula currently returns 10%, not sure what the problem could be.

 

[FranzV]

I think the situation is not very clear. Following the Rules for DAX Code Formatting is always recommended if you want others to understand your code easily. I have some doubts that could help in clarifying the situation.

• Why are you using a calculated column instead of a measure?
• Is [Abs Forecast] a column in the same table?
  • If it is, does it contain the amount you want to compare to the total?
• Are there any other filters you would like to keep or remove other than Projectj56[Prime Cost Filter]=1 ?

Assuming you want to divide the value in each row of Projectj56[Abs Forecast] by the total of the same column (filtered by Projectj56[Prime Cost Filter]=1) I think you could try adding a measure that calculates the total by which you want to divide each row:

TotalAbsForecast :=
CALCULATE (
    SUM ( Projectj56[Abs Forecast] ),
    FILTER ( ALL ( Projectj56 ), Projectj56[Prime Cost Filter] = 1 )
)

Then use it as the denominator for your calculated column:

Projectj56[Forecast %] =
DIVIDE ( Projectj56[Abs Forecast], [TotalAbsForecast] )

I would also recommend you to use helper columns while developing your data model that you can consolidate afterwards into a single calculated column. This will help you ensure that each step of yor calculation returns the expected results and will make debugging easier. For example, you could split your calc column into three steps:

• ForecastPrcNumerator
• ForecastPrcDenominator
• ForecastPrc

Once it is working the way you expect, you can consolidate into a single formula.

I hope it helps.

 

[Sonova]

Hi Franz !

Thanks for taking the time out to write your response, apologies it wasn't clear enough but you assumed correct.

I had tried a similar solution where I created the denominator as a measure based on the filter, this calculated the prime cost correctly (3 767 790), however in the calculated column it would return a percentage of 100% for the given rows.

Measure =Forecast Prime Cost:Forecast Prime Cost:=calculate(sum([Abs Forecast]),filter(Projectj56,[Prime Cost Filter]=1))
Calculated Column =divide([Abs Forecast],[Forecast Prime Cost],0)

I tried your solution however I have realised that what is missing is to sum the prime cost for the given month as it currently calculates the cumulative to date. Thus it would need to calculate the prime costs for 9/1/2015 which is 3 767 790 and not the cumulative to date of 2 76 839 055.

Measure 2:=calculate(sum(Projectj56[Abs Forecast]),filter(all(Projectj56),Projectj56[Prime Cost Filter]=1))

I have included more details based on the table I have.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Prime Cost Filter[/TD]
[TD]Month[/TD]
[TD]ABS Forecast[/TD]
[TD]Required Output[/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]1[/TD]
[TD]9/1/2015[/TD]
[TD]290 308.57[/TD]
[TD]7%[/TD]
[/TR]
[TR]
[TD]1.2[/TD]
[TD]1[/TD]
[TD]9/1/2015[/TD]
[TD]188 700.57[/TD]
[TD]5%[/TD]
[/TR]
</tbody>[/TABLE]

I think the situation is not very clear. Following the Rules for DAX Code Formatting is always recommended if you want others to understand your code easily. I have some doubts that could help in clarifying the situation.

• Why are you using a calculated column instead of a measure?
• Is [Abs Forecast] a column in the same table?
  • If it is, does it contain the amount you want to compare to the total?
• Are there any other filters you would like to keep or remove other than Projectj56[Prime Cost Filter]=1 ?

Assuming you want to divide the value in each row of Projectj56[Abs Forecast] by the total of the same column (filtered by Projectj56[Prime Cost Filter]=1) I think you could try adding a measure that calculates the total by which you want to divide each row:

TotalAbsForecast :=
CALCULATE (
    SUM ( Projectj56[Abs Forecast] ),
    FILTER ( ALL ( Projectj56 ), Projectj56[Prime Cost Filter] = 1 )
)

Then use it as the denominator for your calculated column:

Projectj56[Forecast %] =
DIVIDE ( Projectj56[Abs Forecast], [TotalAbsForecast] )

I would also recommend you to use helper columns while developing your data model that you can consolidate afterwards into a single calculated column. This will help you ensure that each step of yor calculation returns the expected results and will make debugging easier. For example, you could split your calc column into three steps:

• ForecastPrcNumerator
• ForecastPrcDenominator
• ForecastPrc

Once it is working the way you expect, you can consolidate into a single formula.

I hope it helps.

 

[FranzV]

I tried your solution however I have realised that what is missing is to sum the prime cost for the given month as it currently calculates the cumulative to date. Thus it would need to calculate the prime costs for 9/1/2015 which is 3 767 790 and not the cumulative to date of 2 76 839 055.

Ok. If I'm getting this right, when calculating the denominator you need to keep the filter on Projectj56[Month], remove any other filters, and add a filter where Projectj56[Prime Cost Filter] = 1. Try adding a MonthlyForecast column that returns the denominator. This way you can be sure that is being calculated correctly. This formula should work for the MonthlyForecast column:

Projectj56[MonthlyForecast] =CALCULATE (
    SUM ( Projectj56[Abs Forecast] ),
    FILTER ( ALLEXCEPT ( Projectj56, [Month] ), Projectj56[Prime Cost Filter] = 1 )
)

It is very similar to the previous version, but this time we prevent the formula from returning the cummulative forecast by using ALLEXCEPT( Projectj56, [Month] ) instead of ALL( Projectj56 ), keeping the filter on the Month column induced by the current row context.

You can now use this column in your formula to find the desired percentage:

Projectj56[Forecast %] =
DIVIDE ( Projectj56[Abs Forecast], Projectj56[MonthlyForecast] )

If it works correctly, you can swap Projectj56[MonthlyForecast] for its formula and remove the now redundant MonthlyForecast column.

I haven't tried this formulas, so there might be a need to tweak them to get them ti wirk properly. If you can't get them to work, a larger sample of your data could help us create a dummy file to test the formulas or you could upload your file (or a dummy version without any sensitive info) using Dropbox and post the link.

PS. I still don't understand why you need to do this using calc columns instead of measures.

 

[Sonova]

Hi Franz

Thanks so much again for explaining and proposing the recommended formula!! It has worked and I now have the correct % per row. Regarding your query as to why its not a measure, I think it's because I need to show the information on a row by row basis, I tried to do that using a measure but it does not seem appropriate.

I inherited a rather large report which is nothing more than an actual versus budget with a variance and the percentages for each cost category with monthly figures, however this spanned more than 100 columns(as monthly was shown by column) and was not easy to read. My goal was to create a readable workbook which could serve as a template for other projects with the necessary analytics.

I have attached the workbook the report is in the detailedreport tab. The goal is to show the forecast as a % of prime cost, actual as % of actual prime cost, variances (actual -forecast) and if possible to show the previous actual numbers against the current ( I have created a date table and this will be my next challenge). Two reports one detailed by category for year&month(this is where the pain was in the original report as it was shown by column) and the other tab is a cumulative report to date.

I have shared the report so you can get an idea,of what the workbook contains with and what I am trying to do,but dont expect you to help completing, maybe you could just take a look if you have any general recommendations as I am still learning powerpivot and query. You have helped plenty already, if there is a donation box for Mr excel I would be more than happy to donate.


https://www.dropbox.com/s/dgfjpi5b4oiw0kj/ReportBudgetVersusActual.xlsx?dl=0

Ok. If I'm getting this right, when calculating the denominator you need to keep the filter on Projectj56[Month], remove any other filters, and add a filter where Projectj56[Prime Cost Filter] = 1. Try adding a MonthlyForecast column that returns the denominator. This way you can be sure that is being calculated correctly. This formula should work for the MonthlyForecast column:

Projectj56[MonthlyForecast] =CALCULATE (
    SUM ( Projectj56[Abs Forecast] ),
    FILTER ( ALLEXCEPT ( Projectj56, [Month] ), Projectj56[Prime Cost Filter] = 1 )
)

It is very similar to the previous version, but this time we prevent the formula from returning the cummulative forecast by using ALLEXCEPT( Projectj56, [Month] ) instead of ALL( Projectj56 ), keeping the filter on the Month column induced by the current row context.

You can now use this column in your formula to find the desired percentage:

Projectj56[Forecast %] =
DIVIDE ( Projectj56[Abs Forecast], Projectj56[MonthlyForecast] )

If it works correctly, you can swap Projectj56[MonthlyForecast] for its formula and remove the now redundant MonthlyForecast column.

I haven't tried this formulas, so there might be a need to tweak them to get them ti wirk properly. If you can't get them to work, a larger sample of your data could help us create a dummy file to test the formulas or you could upload your file (or a dummy version without any sensitive info) using Dropbox and post the link.

PS. I still don't understand why you need to do this using calc columns instead of measures.

 

[Sonova]

Updated link:

https://www.dropbox.com/s/ym7vz3k0okmka1b/ReportBudgetVersusActual.xlsx?dl=0

 

[FranzV]

Hi Sonova,

Power Query and Power Pivot are two amazing features of Modern Excel, and they will help you accomplish great things if you learn to understand and use them. After playing around with your workbook for a while, I get the impression that you are missing some of the basics of these features and I'd like to recommend some articles that will get you on the right path.

• To understand the differences between Measures and Calc Columns try any (or both) of the articles by The Italians and Rob Collie covering this topic.
• Matt Allington has a nice wrap up of general Best Practices.
• My signature thanks some great Excel/Power BI experts. Try following the links to their sites and exploring their articles. They can help you solve almost any challenge when it comes to Excel.

I uploaded a dummy based on your original file but trying to apply the best practices to the best of my ability. You will notice that there are much fewer columns, but the report uses measures to show the calculations. It is just a start, but hopefully it will be a good starting point that helps you understand how DAX works and build up from there.

If you have questions about the file, let me know.

if there is a donation box for Mr excel I would be more than happy to donate.

We are all people just like you trying to help each other. Helping is a reward in itself.

 

[Sonova]

Thanks Franz, I know there is much to learn, I have ordered the power BI book by Rob Collie. After your comment yesterday I went and further read up on measures and calculated fields which does make more sense now and I see how measures are more preferential opposed to calculated fields should be limited.Again thank you for all your help and guidance.