measure with total by values

[Milozebre]

Hello dear community, 

I call on you because I block. I looked several links here (distinct count group by, ...) and I do not find the good result.

I am a little hopeless. I do not see where I'm wrong.

Can you help me ?

Here is a part of the table that comes from Mysql, not Excel.

I would like to know the number of people who have the schedule 35, 31, .. I have result via a table.

I need it because once I know the number, I could multiply the number of people by their own schedule to find the number of hours worked.

AnneeMois	firstName		Horaires	Attribut	Valeur
2018-08-01	Paul	Paul@coucou.com	32	Lundi	7
2018-08-01	Paul	Paul@coucou.com	32	Mardi	7
2018-08-01	Paul	Paul@coucou.com	32	Mercredi	7
2018-08-01	Paul	Paul@coucou.com	32	Jeudi	7
2018-08-01	Paul	Paul@coucou.com	32	Vendredi	3
2018-08-01	Paul	Paul@coucou.com	32	Samedi	0
2018-08-01	Paul	Paul@coucou.com	32	Dimanche	0
2018-08-01	Valérie	Valérie@coucou.com	32	Lundi	7
2018-08-01	Valérie	Valérie@coucou.com	32	Mardi	7
2018-08-01	Valérie	Valérie@coucou.com	32	Mercredi	7
2018-08-01	Valérie	Valérie@coucou.com	32	Jeudi	7
2018-08-01	Valérie	Valérie@coucou.com	32	Vendredi	0
2018-08-01	Valérie	Valérie@coucou.com	32	Samedi	0
2018-08-01	Valérie	Valérie@coucou.com	32	Dimanche	0
2018-09-01	Valérie	Valérie@coucou.com	32	Lundi	7
2018-09-01	Valérie	Valérie@coucou.com	32	Mardi	7
2018-09-01	Valérie	Valérie@coucou.com	32	Mercredi	7
2018-09-01	Valérie	Valérie@coucou.com	32	Jeudi	7
2018-09-01	Valérie	Valérie@coucou.com	32	Vendredi	0
2018-09-01	Valérie	Valérie@coucou.com	32	Samedi	0
2018-09-01	Valérie	Valérie@coucou.com	32	Dimanche	0
2018-09-01	Paul	Paul@coucou.com	32	Lundi	7
2018-09-01	Paul	Paul@coucou.com	32	Mardi	7
2018-09-01	Paul	Paul@coucou.com	32	Mercredi	7
2018-09-01	Paul	Paul@coucou.com	32	Jeudi	7
2018-09-01	Paul	Paul@coucou.com	32	Vendredi	3
2018-09-01	Paul	Paul@coucou.com	32	Samedi	0
2018-09-01	Paul	Paul@coucou.com	32	Dimanche	0
2018-09-01	Carole	Carole@coucou.com	31	Lundi	7
2018-09-01	Carole	Carole@coucou.com	31	Mardi	7
2018-09-01	Carole	Carole@coucou.com	31	Mercredi	0
2018-09-01	Carole	Carole@coucou.com	31	Jeudi	7
2018-09-01	Carole	Carole@coucou.com	31	Vendredi	7
2018-09-01	Carole	Carole@coucou.com	31	Samedi	0
2018-09-01	Carole	Carole@coucou.com	31	Dimanche	0
2018-08-01	Carole	Carole@coucou.com	31	Lundi	7
2018-08-01	Carole	Carole@coucou.com	31	Mardi	7
2018-08-01	Carole	Carole@coucou.com	31	Mercredi	0
2018-08-01	Carole	Carole@coucou.com	31	Jeudi	7
2018-08-01	Carole	Carole@coucou.com	31	Vendredi	7
2018-08-01	Carole	Carole@coucou.com	31	Samedi	0
2018-08-01	Carole	Carole@coucou.com	31	Dimanche	0
2018-09-01	Clarisse	Clarisse@coucou.com	28	Lundi	7
2018-09-01	Clarisse	Clarisse@coucou.com	28	Mardi	7
2018-09-01	Clarisse	Clarisse@coucou.com	28	Mercredi	7
2018-09-01	Clarisse	Clarisse@coucou.com	28	Jeudi	7
2018-09-01	Clarisse	Clarisse@coucou.com	28	Vendredi	0
2018-09-01	Clarisse	Clarisse@coucou.com	28	Samedi	0
2018-09-01	Clarisse	Clarisse@coucou.com	28	Dimanche	0
2018-09-01	Marylle	Marylle@coucou.com	28	Lundi	7
2018-09-01	Marylle	Marylle@coucou.com	28	Mardi	7
2018-09-01	Marylle	Marylle@coucou.com	28	Mercredi	0
2018-09-01	Marylle	Marylle@coucou.com	28	Jeudi	7
2018-09-01	Marylle	Marylle@coucou.com	28	Vendredi	7

here the table in Power BI without measure :

Line : Horaire

Values Number of user mail

I need a measure or colonne to obtain this : 

Horaires	Nb of email	Total
28	8	224
31	1	31
32	2	64
35	468	16380

Then I could then calculate the rate. The time they actually worked divided by the theoretically worked time.

Thank you in advance

[Anonymous · Accepted solution]

HI @Milozebre,

You can use below formula to get total value based on horaires and distinct email count:

Total =
DISTINCTCOUNT ( Table2[Email] ) * SELECTEDVALUE ( Table2[Horaires] )

Regards,
Xiaoxin Sheng

[Milozebre]

@Anonymous

Hello Thank you for answer. its working

Is it possible to get the total ? 

I'm sorry if I bother you. But I have to provide this information. I didn't create these tables and I started with DAX. The ultimate goal is to have the total hours worked and to have the theoretically worked total time and then make the rate.

Many thanks in advance

[Anonymous]

HI @Milozebre,

Please share a pibx file with sample data and expected result for test and coding formula:

Regards,

Xiaoxin Sheng