Trying to sum events by Region Manager

[brockry1]

I hvae this as my DAX formula and it is working to bring back all the events completed.

# of completed events/Visits = CALCULATE(DISTINCTCOUNT('Event & Task'[ID]),'Event & Task'[Status (Consolidated)]="Completed")

The Region Manager is in my "Account" table.

How do I add to the formula to sum by Region? 

[Anonymous · Accepted solution]

Hi @brockry1 ,
Can't determine what your data structure looks like, here's a sample of data created from your description.
Event & Task

ID	Status (Consolidated)	Key
1001	Completed	1
1002	Incompleted	1
1001	Completed	1
1002	Completed	2
1003	Completed	2
1004	Completed	2
1005	Completed	3
1006	Completed	4
1006	Incompleted	4
1006	Incompleted	4

Account

Region	Key
A	1
B	2
C	3
D	4

Make sure there is a relationship between the two tables

Create a measure

EventCount = 
CALCULATE(
    DISTINCTCOUNT('Event & Task'[ID]),
    FILTER(
        ALLEXCEPT(
           'Event & Task',
            Account[Key]
        ),
        'Event & Task'[Status (Consolidated)] = "Completed"
    )
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[LYPowerBI]

CALCULATE(
DISTINCTCOUNT('Event & Task'[ID]),
'Event & Task'[Status (Consolidated)]="Completed",
'Account'[Region Manager] = "xxx")

Does this work for your question?

[LYPowerBI]

I did not 100% understand your question. but one way to calculate the #Event completed by region manager xxxxx might be = CALCULATE(
DISTINCTCOUNT('Event & Task'[ID]),
'Event & Task'[Status (Consolidated)]="Completed",
'Account'[Regino Manger] = "xxxxx"),
Depending on you, you may consider to add KEEPFILTER on the filter expression.