Forum Discussion
Grouping calculated measures
Hello, I am trying to create a calculated measure that takes the sum of a consumer action and divides it by the unique number of consumers in a group, with the ability to track it by week and by ...
Hi Anonymous ,
I created some data:
Here are the steps you can follow：
1. Create calculated column.
B.Week = WEEKNUM('Group_Table'[Date],2 )2. Create measure.
Sum of actions by group 1 in week = CALCULATE(SUM('Group_Table'[amount]),FILTER(ALL(Group_Table),'Group_Table'[Group]=MAX('Group_Table'[Group])&&'Group_Table'[B.Week]=MAX('Group_Table'[B.Week])&&'Group_Table'[Action]=MAX('Group_Table'[Action])))Number of unique group 1 consumers in week = CALCULATE(DISTINCTCOUNT('Group_Table'[Date]), FILTER(ALL('Group_Table'), 'Group_Table'[Group]=MAX('Group_Table'[Group])&&'Group_Table'[Action]=MAX('Group_Table'[Action])))Final measure (C/D) = DIVIDE([C. Sum of actions by group 1 in week],[D. Number of unique group 1 consumers in week])3. Use [group] as the slicer.
4. Result:
When selecting group =1:
When selecting group =2:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Anonymous ,
I created some data:
Here are the steps you can follow：
1. Create calculated column.
B.Week = WEEKNUM('Group_Table'[Date],2 )2. Create measure.
Sum of actions by group 1 in week =
CALCULATE(SUM('Group_Table'[amount]),FILTER(ALL(Group_Table),'Group_Table'[Group]=MAX('Group_Table'[Group])&&'Group_Table'[B.Week]=MAX('Group_Table'[B.Week])&&'Group_Table'[Action]=MAX('Group_Table'[Action])))Number of unique group 1 consumers in week =
CALCULATE(DISTINCTCOUNT('Group_Table'[Date]),
FILTER(ALL('Group_Table'),
'Group_Table'[Group]=MAX('Group_Table'[Group])&&'Group_Table'[Action]=MAX('Group_Table'[Action])))Final measure (C/D) =
DIVIDE([C. Sum of actions by group 1 in week],[D. Number of unique group 1 consumers in week])3. Use [group] as the slicer.
4. Result:
When selecting group =1:
When selecting group =2:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly