2nd Level aggregate on specific groups
I have a requirement which needs aggregation to be applied, on aggregated data, on specific groups. Something similar to the pseudo-SQL statement below.
SELECT Group1, COUNT(Group2), COUNT(condition which filters certain Group2s)
FROM (SELECT Group1, Group2, Group3, COUNT(Attribute)
GROUP BY Group1, Group2, Group3)
GROUP BY Group1
Further description on the need:
PFA. Consider data in Granular Data table.
Based on that data, I would like to find number of users per region whose order count equals corresponding region's order count.
1) This column should be reactive to dashboard filters. So, this should be calculated dynamically as an insight formula column.
2) Column level filter should not be used, as this is just a part of the formula and other part needs total rows information.
I have been trying to achieve this for a while. It would be really helpful, if I get a clue.
Thanks in advance.