I have this calculation in QS for the below screenshot, on expanding, the values are accurate, when I collapse, I get the wrong result. Any idea how to fix it?
sum(
maxOver({Days to Close},[opportunityid,{gtm_team}],PRE_AGG)/
countOver(1,[opportunityid,{gtm_team}],PRE_AGG))/ distinct_count(opportunityid)
Hello @sarawgia !
The collapsed column/row will default to a sum or however the field is aggregated.
You can try using the format option the “Hide” the collapsed value:
Let me know fi this helps!
Hi Duncan, thank you for your reponse. However, I am not looking to hide the collapsed value, is there a way to get accurate results using the above formula for collapsed and expanded values?
Hello @sarawgia, what are you wanting it to show when you collapse the pivot table? The default functionality will sum all of the row values in that column when you collapse to show the total for the group.
You could maybe try to add an avg function instead of sum on the calculated field if that would be more of what you are looking for when the group is collapsed.
Hi Dylan, so when I add gtm_team granularity to this formula - sum(
maxOver({Days to Close},[opportunityid,{gtm_team}],PRE_AGG)/
countOver(1,[opportunityid,{gtm_team}],PRE_AGG))/ distinct_count(opportunityid)
my expand values are correct but collapsed value(wwso service group) is inaccurate, it should be 123 instead of 148. When I switch gtm_team with {WWSO Service Group} in this formula - sum(
maxOver({Days to Close},[opportunityid,{WWSO Service Group}],PRE_AGG)/
countOver(1,[opportunityid,{WWSO Service Group}],PRE_AGG))/ distinct_count(opportunityid)
my collapsed value{WWSO Service Group} is correct but expanded values(gtm_team) are incorrect. So not sure how to fix this
Hello @sarawgia, if you look at the calculated field in your field well are you able to switch it to average? That might resolve the issue when collapsing that section of the table.
Hello @sarawgia, did my response help you find a solution to the issue you were facing in QuickSight? If so feel free to mark it as a solution or let me know if there is more information so we can find an answer.
Hi Dylan, thank you for your response. When I switched it to Average it did not work. I just had to create 2 separate visuals instead