Forum Discussion

Chavas's avatar
Chavas
Helper II
5 years ago
Solved

Get average based on another table values

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

SubdepartmentPopulationSubmited
Sellers53
Administration32

1 per persons

IDDepartmentSubdepartment
1SalesSellers
2AdministrationAccountability

1 per answers of a survey (AKA NPSData)

 
IDCategoryQuestionAnswerNPS Value
26Work environmentq12Detractor
42Work environmentq12Detractor
49Work environmentq22Detractor
64Work environmentq12Detractor
28Work environmentq24Detractor

 

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
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    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

2 Replies

  • Chavas's avatar
    Chavas
    Helper II
    5 years ago

    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?
  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    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