Count the number of selected value with the same name in the slicer

[razieh1990]

Hello 

I have a table with four columns, the first column is name,

the second column is ID,

the third column is the worked hours

and the fourth column is department.

I created a matrix visual in Power BI and one slicer. the slicer value is ID (which is unique, while the name may be the same under a different ID). in other words, this data is for three employees who work for different departments under different IDs.

the Matrix visual row is the department and the values are the average hour.

Question: The slicer value is ID and it is possible we have different IDs under the same name.

when I chose 10 IDs from these 10 IDs 5 of them are for name "Ali", 3 of them have same name as"Zahra" and 2 of them have same name as "Mina".

in the matrix visual I want to create a new measure that sum of all "Ali" 's hours and divide it by the count of Ali's name among the IDs selected and display it in front of all the departments Ali works and do the same for Zahra and Mina .
and if two employees work for the same departement , initially it should calculate the  total ali Hours (based the IDs we selected) divided to the how many "Ali" names are selected when we select the IDs from the slicer and then do the same for other employee and then again average their hour work and display in front of department

[talespin · Accepted solution]

hi @razieh1990 

Please try this measure. Replace table name and column name with yours.

New Measure =

VAR _CountSelected = COUNTX(VALUES('Table'[ID]), 1)

VAR _SummTable =

ADDCOLUMNS(

            ADDCOLUMNS(

                        SUMMARIZE('Table', 'Table'[Name], 'Table'[Department], 'Table'[Hour]),

                        "@Count", CALCULATE(COUNT('Table'[Name]), REMOVEFILTERS('Table'[Department]))

            ),

            "@DivideHours", DIVIDE([Hour], [@Count])

)

RETURN SUMX(_SummTable, [@DivideHours])

[Anonymous]

Hi, @razieh1990 

According to your description, there are some things that I didn't read that I hope you can explain, does Slicer have a restriction on single or multiple selections, and if it is multiple selections, how do you deal with averages when different IDs (each belonging to a different Name) are selected.
You can share the pbix file without sensitive data or post a complete reproduction of the problem with the data table, measure, desired effect, and the logic to achieve the effect and other information.

Best Regards,
Yang
Community Support Team