Hi everyone:
I’m trying to create a calculated field that shows the difference between the data in a table to the average of the data.
I’m able to display commissions by client in the table by using a calculated field:
Field1 = Sum({commissions})
and I’m able to display the average commissions by client in the same table by using the calculated field:
Field2 = avg({commissions}, [{client}])
But when I try subtracting the two fields it displays the error “the calculation operated on LAC agg expressions is not valid”.
Is there a way around this issue?
Thanks!
Hello @skoenig99 - Can you please try put the following calculation
sum(commissions, [client]) - avg(commissions, [client])
I tried to replicate something similar and it is working fine for me. Please let me know. Hope this helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
Thanks for the response. I tried what you suggested and I’m getting the following error:
@skoenig99 - Can you please share the sample snapshot of the table visual and the calculation that you are trying to use? This should not create problem ideally. As you can from the below snapshot, it renders values perfectly!!
I’m using this in a pivot table where the rows are the client and columns are the date. Should have specified that earlier.
Calculation you suggested is: Sum({Comm}, [{Dynamic Client Group}]) - avg({Comm}, [{Dynamic Client Group}])
Here’s the table:
My original calculation was: Sum({Comm}) - avg({Comm}, [{Dynamic Client Group}])
Hello @skoenig99 - Since your calculation uses both the dimensions i.e. Client group and Date, ideally you should include both the dimensions over there. I tried to use similar calculations at my end and facing no problems in executing them. Please take a look at the below snapshot. Hope this helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
The problem is that I need the average of the data shown in the chart, not the individual data in the dataset. So in your example for Communications it would be the average of 5738.31 and 7119.05, or 6428.68. In the end, I’m aiming towards getting a % variance from that average.
Thank you @skoenig99 for your response. Since you provided the example snapshot of a Pivot Table, I tried that one out. Can you please share the exact construct of your visual i.e. Chart Type and corresponding field wells? That would help to replicate at my end.
1 Like
I started a clean sheet for this just in case something else was causing the issue.
The chart I’m using is Pivot Table.
Here’s the field wells and visual:
Comm by Month = Sum(ComCollectedIncome, [ClientRAP, {Assign Date}])
Avg Comm = avgOver(Sum(ComCollectedIncome), [ClientRAP])
When I try to create a calculated field for the variance between the above fields, I get this error:
Hello @skoenig99 - I have tried to replicate it at my end and was able to successfully get it. Please see the below snapshot with the reference sample calculations. Hope this helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
Hoping solution provided by Sagmukhe works. I am marking the reply as “Solution,” but let us know if this is not resolved.
Thanks for posting your questions on the QuickSight CommunityQ&A Forum!
