Get average based on another table values

[Chavas]

Hello, I am facing a problem that it should be really easy and there should be a lot of information but I have not found it so something I should be doing wrong.

I have a model with 3 tables
1 per department

Subdepartment	Population	Submited
Sellers	5	3
Administration	3	2

1 per persons

ID	Department	Subdepartment
1	Sales	Sellers
2	Administration	Accountability

1 per answers of a survey (AKA NPSData)

ID	Category	Question	Answer	NPS Value
26	Work environment	q1	2	Detractor
42	Work environment	q1	2	Detractor
49	Work environment	q2	2	Detractor
64	Work environment	q1	2	Detractor
28	Work environment	q2	4	Detractor

Department is related with persons and persons are related with NPSData

Then I am creating a visual to show the NPS per question and I would like to know the % of promoters or Detractors based on the department population (As far as not every person on the department has fulfilled the survey) so I can create an slicer to navigate through the subdepartments to get the % of Promoters/Neutrals/detractors by question by department.

So at the end, within a visual like this barchar

Left- Graph visual Right- Table

On the right side is a table, and I will only need to add a measure which divides each of this column, but I am stucked o nhow to do it.

I have tried something like:

DIVIDE(

    COUNTA('NPSDATA'[NPS Value]),

    VALUES('Department'[DptPopulation])

)

But it does not work. Could you please help me? Thanks in advance

[v-easonf-msft · Accepted solution]

Hi, @Chavas 

You need to modify your second measure slightly.If you want to change  multiply this value by 100 to show it in % ,it is recommended that you do not use the Text "nan" in the third parameter of function "Divide".You cannot mix different types of data in one column if you want to  show it in %.

measure =
DIVIDE (
    COUNTA ( NPSDATA[Category] ),
    SELECTEDVALUE ( Department[DptPopulation] )
)

In addition ,please check whether the field "Subdepartment" in you left graph visual are the same as the field "Subdepartment"  in right Table viusal  

Best Regards,
Community Support Team _ Eason

[Chavas]

Okay I achieved to do it in the table
DAX:

(DIVIDE(counta(NPSDATA[Category]),SELECTEDVALUE(Department[DptPopulation]),"nan"))

But I cannot use it on the graph side, it si not displaying anything.
Also I cannot multiply this value by 100 to show it in %

I am missing somthing?