topic Re: sum of the average sum in DAX Commands and Tips

sum of the average sum

antoinersw — Tue, 24 Jan 2023 17:44:12 GMT

hello, I have the followind data :

Client	Numéro	_€ Projeté et signés	heures_total_presta	Duree presta	heures_quantity	_Heures projetés signé	prix_heure_HT_unitaire_avec_remise	montant_HT_avec_remise
98457057	DE2300344	18513	671	330	122	335.5	54	3366
98457057	DE2300345	19173	671	330	122	335.5	56	3486
98457057	DE2300052	13689.6	466.1333333333334	368	76	233.0666666666667	58	2232
98457057	DE2300053	13014.933333333334	466.1333333333334	368	76	233.0666666666667	56	2122
98310560	DD2300269	5637.5	176	165	32	176	33	1025
98310560	DD2300063	5152.166666666666	164.66666666666666	95	52	164.66666666666666	31	1627
98310560	DD2300064	4563.166666666666	164.66666666666666	95	52	164.66666666666666	27	1441
98310560	DD2300270	3056.6	108.8	102	32	108.8	29	899

I need the following result :

I tried the following formula

var e = sumx( values('leads'[Client]), AVERAGEX( leads, CALCULATE( sumx( FILTER( leads, 'leads'[heures_total_presta] = max('leads'[heures_total_presta])) , leads[Duree presta]/30 * 'leads'[montant_HT_avec_remise]), 'leads'[Date d acceptation] <> BLANK() )) ) return e

Please advise me a working formula, I m desperate

Re: sum of the average sum

tamerj1 — Tue, 24 Jan 2023 17:51:28 GMT

Hi antoinersw
Missing one CALCULATE. Please try

_€ Projeté et signés = SUMX ( VALUES ( 'leads'[Client] ), CALCULATE ( AVERAGEX ( VALUES ( 'leads'[Numéro] ), CALCULATE ( SUMX ( TOPN ( 1, leads, 'leads'[heures_total_presta] ), leads[Duree presta] / 30 * 'leads'[montant_HT_avec_remise] ), 'leads'[Date d acceptation] <> BLANK () ) ) ) )

Re: sum of the average sum

antoinersw — Wed, 25 Jan 2023 09:35:19 GMT

Thanks a lot tamerj1 🙂

Do you happen to know how "hard" that formula is on the scale of the difficulty of DAX ?

Re: sum of the average sum

tamerj1 — Wed, 25 Jan 2023 09:37:47 GMT

antoinersw
Average or perhaps little hard but definitely not easy.

Re: sum of the average sum

antoinersw — Wed, 25 Jan 2023 10:25:33 GMT

I ve actually changed it a little to fit my usecase and take the top1 for the averageX

_€ Projeté et signés =

var e =

SUMX (

VALUES ( 'leads'[Client] ),

CALCULATE (

AVERAGEX (

// VALUES ( 'leads'[Numéro] )

TOPN ( 1, leads, 'leads'[heures_total_presta] ),

CALCULATE (

SUMX (

TOPN ( 1, leads, 'leads'[heures_total_presta] ),

leads[Duree presta] / 30 * 'leads'[montant_HT_avec_remise]

'leads'[Date d acceptation] <> BLANK ()

)

return e