Thanks for posting. I used this approach. In my sample data crtest08 was $0. The average with zeroes was $4316, without $4586. Here's my measure: =CALCULATE( AVERAGEX(Table1,Table1[Column1]), FILTER( ALLEXCEPT ( Table1,Table1[Data]), Table1[Column1] 0) )
Hey Mr.Excel, Here’s a DAX-measure remark: you can format the result of a DAX-measure exactly the way you want, and that it persistent in the pivot table. Yet another reason why you should work with explicit measures. :-) Maybe unconventional, but in this case I would produce the entire table with all the trimmings in DA - dynamic arrays and make it a single cell formula (perhaps hide its complexity behind a LAMBDA custom function that takes the entire table as the only argument). If you send me the table I’ll build it for you.
Using the same table name and headers, here's a spilled dynamic array formula that will create the same results. No refresh needed. Would need some serious conditional formatting to simulate automatic pivot table formatting. You should be able to plug-and-play with this in your spreadsheet. It responds to data changes as well as table name and header name changes. It also responds to filtering the table, such as with slicers. It was a fun exercise for dynamic array formulas. I tested this against 500,000 rows of data. It seemed to perform just a little slower than a DAX measure. The key advantage is that the formula responds to data changes instantly. You don't have to refresh the data model. =LET(svc_hdr,TOROW(UNIQUE(SORT(FILTER(LCosts[Service],BYROW(LCosts[Service],LAMBDA(x,AGGREGATE(3,5,x)))=1)))), blank_arr,EXPAND({""},,COUNTA(svc_hdr),""), top_hdr,HSTACK("",LCosts[[#Headers],[Service]],blank_arr), proj_hdr,LCosts[[#Headers],[Project]], next_hdr,HSTACK(proj_hdr,svc_hdr,LCosts[[#Headers],[Project]]&" Total"), hdrs,VSTACK(top_hdr,next_hdr), proj_lbls,SORT(UNIQUE(FILTER(LCosts[Project],BYROW(LCosts[Project],LAMBDA(x,AGGREGATE(3,5,x)))=1))), proj_svc_costs,SUMIFS(LCosts[Amount],LCosts[Project],proj_lbls,LCosts[Service],svc_hdr), proj_tot_costs,BYROW(proj_svc_costs,LAMBDA(x,SUM(x))), cost_breakout,HSTACK(proj_lbls,proj_svc_costs,proj_tot_costs), svc_tot_costs,BYCOL(proj_svc_costs,LAMBDA(x,SUM(x))), svc_tot_row,HSTACK(LCosts[[#Headers],[Service]]&" Total",svc_tot_costs,SUM(svc_tot_costs)), svc_avg_costs,BYCOL(proj_svc_costs,LAMBDA(x,AVERAGE(FILTER(x,x0)))), svc_avg_row,HSTACK(LCosts[[#Headers],[Service]]&" Average",svc_avg_costs,AVERAGE(FILTER(proj_tot_costs,proj_tot_costs0))), VSTACK(hdrs,cost_breakout,svc_tot_row,svc_avg_row))
If you create a sample table with the same column names and name it LCosts, it'll work like a champ. Just copy the formula into a cell on the same or on a different sheet to see the results. It will respond instantly to table filtering, including with slicers.
Very informative. What I hate is, when am using multiple pages of dashboards and spent hours and days building the report, only to discover I need to do it all over again because what? I did not check the "Add Data to Data Model" box!! Very annoying tbh...
With the new dynamic array formulas I've gone away from pivot tables every chance I get. No need to refresh. Can use # reference to sum, average, whatever you need using standard Excel functions. So much simpler with only a minor loss in flexibility around filtering and expand/collapse.
You can use the function ISFILTERED to avoid calculating values other than totals. IF ( ISFILTERED ( LCosts [Project]), blank(), # insert your calculation here # )) to display values only when Project is not filtered (totals)
This is the simplest scalable solution. Then you just need to apply your formatting on your measures rather than on the cells and it will behave exactly as you want.
Hi Mr. Excel, thanks for your videos, that's helpful for me to do my works. Regarding this video, assuming you have several same service on the same launch (eg: several "service A" on "Launch 1" or several "service E" on "Launch 2"). Then also, you want an average amount per service per Launch, instead a sum amount. What is the additional function(s) that i have to input ? Kindly help to share the new functions for this scenario. Thanks in advance for noticing and answering my question.
I'm fairly good with Dax, and I've definitely done something similar. Unfortunately, I'm not going to get a chance to look at it today. Maybe Monday. The whole internet will have created a more beautiful solution for you by then though.
Hi Mr. Excel, I tried to use DAX formula (Data model) in the same workbook where I use normal Pivot Table. But every time there seems to be a conflict where the Power Pivot stops running after a couple of times. Any thoughts?
I would call that check box "Give me extra Power to pivot table". XD I think we can do it with regular Pivot Table, IF we are not facing the +ve and -ve balancing out issue in the source table. The average won't count values if they are not present as zero. The layout of the pivot table would be "Project" under Column; "Launch" and "Values" under Row; "Sum of value" and "Average of value2" under Values. Then we need to apply a regular autofilter to the pivot table and then filter out those "average of values". Not ideal when new data comes in as we have to re-apply the autofilter, but that is similar to using "Set". And also, we need to replace all "0" values, if present, to blank; and it won't work when there are positive and negative values adding up to zero... how to solve that? Power Query to aggregate the dataset first before putting it to pivot table. :) I am not saying DAX is not good. Indeed I love both Power Query and Power Pivot with DAX. They are SUPER POWERFUL when being used together!
Thanks for this. After seeing the solutions proposed, the OP said "Wow - amazing! But now that I see these, I will keep using AVERAGEIFS outside of the pivot table."
Wow makes my brain hurt. 😁 Just wondering though, would your average calculation still be off b/c while an entry of +100 is offset by an entry of -100 and you entered a correcting entry of +120 for a launch service combination resulting in a net total of +120 but with a row count of 3. Yeilding an average of +40 (120/3)? Seems like you would an answer of +120 average. (120/1). I may have it all wrong.
I sort of wondered about this at first, but in doing the math, it is only counting Launch 1 as one record. It must be the DISTINCTCOUNT in the first formula that is counting (Launch 1, Launch 1, Launch 1, Launch 2, Launch 2) as 2 distinct values.
I think you will be able to edit the set in mdx to return the children of project rather than manually adding a new launch manually. I have not tried it but if you replace all of the script from .&[launch1]… all the way to …&[launchA] with .Children that should work.
A B C 20 40 30 (10) 50 [60 20 30 20 50 10 10 60 10 80 30 (90) 50] 20 20 70 From column A min value 10 to column b max value 90, how can I add column c all cells from max value to min value at column A to column B.
Thank you for the pivot table filter trick to remove cells with 0s!!!!
Thanks for posting. I used this approach. In my sample data crtest08 was $0. The average with zeroes was $4316, without $4586. Here's my measure: =CALCULATE(
AVERAGEX(Table1,Table1[Column1]),
FILTER( ALLEXCEPT ( Table1,Table1[Data]), Table1[Column1] 0)
)
Data Column1
crtest01 6627
crtest02 4456
crtest03 5242
crtest04 5600
crtest05 5977
crtest06 7266
crtest07 4756
crtest08 0
crtest09 4762
crtest10 1845
crtest11 2476
crtest12 2933
crtest13 7888
crtest14 1139
crtest15 4531
crtest16 6153
crtest17 1734
Hey Mr.Excel,
Here’s a DAX-measure remark: you can format the result of a DAX-measure exactly the way you want, and that it persistent in the pivot table. Yet another reason why you should work with explicit measures. :-)
Maybe unconventional, but in this case I would produce the entire table with all the trimmings in DA - dynamic arrays and make it a single cell formula (perhaps hide its complexity behind a LAMBDA custom function that takes the entire table as the only argument).
If you send me the table I’ll build it for you.
The format panel in Dax Measure doesn’t offer #,##0,K unless I am missing it.
Using the same table name and headers, here's a spilled dynamic array formula that will create the same results. No refresh needed. Would need some serious conditional formatting to simulate automatic pivot table formatting. You should be able to plug-and-play with this in your spreadsheet. It responds to data changes as well as table name and header name changes. It also responds to filtering the table, such as with slicers. It was a fun exercise for dynamic array formulas.
I tested this against 500,000 rows of data. It seemed to perform just a little slower than a DAX measure. The key advantage is that the formula responds to data changes instantly. You don't have to refresh the data model.
=LET(svc_hdr,TOROW(UNIQUE(SORT(FILTER(LCosts[Service],BYROW(LCosts[Service],LAMBDA(x,AGGREGATE(3,5,x)))=1)))),
blank_arr,EXPAND({""},,COUNTA(svc_hdr),""),
top_hdr,HSTACK("",LCosts[[#Headers],[Service]],blank_arr),
proj_hdr,LCosts[[#Headers],[Project]],
next_hdr,HSTACK(proj_hdr,svc_hdr,LCosts[[#Headers],[Project]]&" Total"),
hdrs,VSTACK(top_hdr,next_hdr),
proj_lbls,SORT(UNIQUE(FILTER(LCosts[Project],BYROW(LCosts[Project],LAMBDA(x,AGGREGATE(3,5,x)))=1))),
proj_svc_costs,SUMIFS(LCosts[Amount],LCosts[Project],proj_lbls,LCosts[Service],svc_hdr),
proj_tot_costs,BYROW(proj_svc_costs,LAMBDA(x,SUM(x))),
cost_breakout,HSTACK(proj_lbls,proj_svc_costs,proj_tot_costs),
svc_tot_costs,BYCOL(proj_svc_costs,LAMBDA(x,SUM(x))),
svc_tot_row,HSTACK(LCosts[[#Headers],[Service]]&" Total",svc_tot_costs,SUM(svc_tot_costs)),
svc_avg_costs,BYCOL(proj_svc_costs,LAMBDA(x,AVERAGE(FILTER(x,x0)))),
svc_avg_row,HSTACK(LCosts[[#Headers],[Service]]&" Average",svc_avg_costs,AVERAGE(FILTER(proj_tot_costs,proj_tot_costs0))),
VSTACK(hdrs,cost_breakout,svc_tot_row,svc_avg_row))
Don't know if or how this works, but it was worth a like!
If you create a sample table with the same column names and name it LCosts, it'll work like a champ. Just copy the formula into a cell on the same or on a different sheet to see the results. It will respond instantly to table filtering, including with slicers.
Very informative. What I hate is, when am using multiple pages of dashboards and spent hours and days building the report, only to discover I need to do it all over again because what? I did not check the "Add Data to Data Model" box!! Very annoying tbh...
With the new dynamic array formulas I've gone away from pivot tables every chance I get. No need to refresh. Can use # reference to sum, average, whatever you need using standard Excel functions. So much simpler with only a minor loss in flexibility around filtering and expand/collapse.
Me, too. You can see how I solved this with a single dynamic array formula in my comment. You're right about filtering, formatting, etc.
You can use the function ISFILTERED to avoid calculating values other than totals.
IF ( ISFILTERED ( LCosts [Project]), blank(), # insert your calculation here # )) to display values only when Project is not filtered (totals)
This is the simplest scalable solution. Then you just need to apply your formatting on your measures rather than on the cells and it will behave exactly as you want.
Hi Mr. Excel, thanks for your videos, that's helpful for me to do my works.
Regarding this video, assuming you have several same service on the same launch (eg: several "service A" on "Launch 1" or several "service E" on "Launch 2").
Then also, you want an average amount per service per Launch, instead a sum amount.
What is the additional function(s) that i have to input ? Kindly help to share the new functions for this scenario.
Thanks in advance for noticing and answering my question.
I'm fairly good with Dax, and I've definitely done something similar. Unfortunately, I'm not going to get a chance to look at it today. Maybe Monday. The whole internet will have created a more beautiful solution for you by then though.
Hi Mr. Excel, I tried to use DAX formula (Data model) in the same workbook where I use normal Pivot Table. But every time there seems to be a conflict where the Power Pivot stops running after a couple of times. Any thoughts?
I would call that check box "Give me extra Power to pivot table". XD
I think we can do it with regular Pivot Table, IF we are not facing the +ve and -ve balancing out issue in the source table. The average won't count values if they are not present as zero. The layout of the pivot table would be "Project" under Column; "Launch" and "Values" under Row; "Sum of value" and "Average of value2" under Values. Then we need to apply a regular autofilter to the pivot table and then filter out those "average of values". Not ideal when new data comes in as we have to re-apply the autofilter, but that is similar to using "Set".
And also, we need to replace all "0" values, if present, to blank; and it won't work when there are positive and negative values adding up to zero... how to solve that? Power Query to aggregate the dataset first before putting it to pivot table. :)
I am not saying DAX is not good. Indeed I love both Power Query and Power Pivot with DAX. They are SUPER POWERFUL when being used together!
Thanks for this. After seeing the solutions proposed, the OP said "Wow - amazing! But now that I see these, I will keep using AVERAGEIFS outside of the pivot table."
@@MrXL indded I prefer to have AVERAGEIF too
Wow makes my brain hurt. 😁 Just wondering though, would your average calculation still be off b/c while an entry of +100 is offset by an entry of -100 and you entered a correcting entry of +120 for a launch service combination resulting in a net total of +120 but with a row count of 3. Yeilding an average of +40 (120/3)? Seems like you would an answer of +120 average. (120/1). I may have it all wrong.
I sort of wondered about this at first, but in doing the math, it is only counting Launch 1 as one record. It must be the DISTINCTCOUNT in the first formula that is counting (Launch 1, Launch 1, Launch 1, Launch 2, Launch 2) as 2 distinct values.
Hey Mr. Excel, would it be possible to download the sample file?
I think you will be able to edit the set in mdx to return the children of project rather than manually adding a new launch manually. I have not tried it but if you replace all of the script from .&[launch1]… all the way to …&[launchA] with .Children that should work.
Wow! This would be a hot hot hot trick. I will try it in the morning. Thanks for posting it.
A B C
20 40 30
(10) 50 [60
20 30 20
50 10 10
60 10 80
30 (90) 50]
20 20 70
From column A min value 10 to column b max value 90, how can I add column c all cells from max value to min value at column A to column B.