CODEWITHSUNDEEP
powerbidax
Ash W
Ash W

Reputation: 30

How To Calculate the Sum Divided by Distinct Count?

I have the table below. Basically I want to calculate the calls answered by each ID within each hour. enter image description here

In other words, I am trying to use the charts above divided by the charts below (different colors represent their groups - A or B). For example, in the chart I actually want to get, the two bars for 00:00-00:59 should be 1.125 (blue) and 7 (yellow). enter image description here

I wrote the following DAX yet the result is not correct:
Calls Answered by Unique IDs = SUM('Table'[Calls (Answered)]) / DISTINCTCOUNT('Table'[ID])

Could someone tell me how to do it properly? Many thanks!!!

Upvotes: 0

Views: 1083

Answers (1)

smpa01
smpa01

Reputation: 4282

Can you please try this measure

Measure =
VAR _partition =
    ALLEXCEPT ( tbl, tbl[Hour Period], tbl[Group] )
VAR _sum =
    CALCULATE ( SUM ( tbl[Calls (Answered)] ), _partition )
VAR _count =
    CALCULATE ( DISTINCTCOUNT ( tbl[ID] ), _partition )
RETURN
    DIVIDE ( _sum, _count )

Upvotes: 1

Related Questions