topic Re: Issue with sumx in DAX Commands and Tips

Issue with sumx

Anonymous — Mon, 06 Jul 2020 10:08:21 GMT

Hello all,

I've tried to put this in many ways by my own, but i'm still struggling to solve this problem. Sorry by advance if it seems like a beginer question. I hope somebody knows the answer here. I have a measure calculation rule which is :

Rouge2 = Calculate(

IF(

(QUOTIENT(SUMX('Table2','Table2'[Xp]-'Table2'[prérequis]),12)+1) >= 5,

QUOTIENT(SUMX('Table2','Table2'[Xp]-'Table2'[prérequis]),12)+1)

*SUMX('Table2','Table2'[Weight])),

Codetable2[Couleur]="R")

[Xp], [Requis] and [Weight] are Whole number datatype and

Table2(Many to) is linked to Codetable2(1)

My problem is that when I try to display this mesaure by an other text column ' C1' of table2. base

I have right now : 3+3 =6 >=5 --> 5 * (10+5) = 75

I would like to have : 3*10 + 3*5 = 45

Do you have the solution ?

Thank you

Re: Issue with sumx

AntrikshSharma — Mon, 06 Jul 2020 10:38:25 GMT

Maybe try something like this:

Rouge2 = SUMX ( VALUES ( 'Table2'[C1] ), CALCULATE ( VAR AA = QUOTIENT ( SUMX ( 'Table2', 'Table2'[Xp] - 'Table2'[prérequis] ), 12 ) + 1 VAR BB = IF ( AA >= 5, 5, AA * SUMX ( 'Table2', 'Table2'[Weight] ) ) RETURN BB, Codetable2[Couleur] = "R" ) )

Re: Issue with sumx

Thejeswar — Mon, 06 Jul 2020 11:11:26 GMT

Hi Anonymous ,

Can you give some more details on your isse.

If possible, can you give a sample data of your tables and an expected result example

Re: Issue with sumx

Anonymous — Mon, 06 Jul 2020 15:12:41 GMT

Hi Antriksh Sharma,

Thank you for your reply and the tips for writting in dax.
Unfortunately it's not working because I need in BB var that the 1st condition equal to 5 must be multipied by Table2[Weight] also.

😕

However, here is some data sample

	Xp	Prérequis	Weight	Points(Rouge2)	Competencies
Value 1	36	12	10	(Q(36-12,12)+1)*10=30	C1
Value 1	72	12	5	(Q(72-12,12)+1*5 = 25	C2
Value 1	6	12	10	0	C3

Re: Issue with sumx

Anonymous — Mon, 06 Jul 2020 16:00:17 GMT

Thanks lot for your help,

I found the solution it's :

Rouge2 =

SUMX (

DISTINCT('Lot 2'[Competence]),

CALCULATE (

VAR CC =

SUMX('Lot 2','Lot 2'[XP]-'Lot 2'[Prérequis])

VAR AA =

IF ( CC <0,0,QUOTIENT(CC, 12) + 1)

VAR BB =

IF ( AA >= 5,5*SUMx ('Lot 2','Lot 2'[Weight]), AA * SUMX ( 'Lot 2','Lot 2'[Weight]) )

RETURN

BB,

Codelot2[Couleur]= "R"

)

Re: Issue with sumx

AntrikshSharma — Mon, 06 Jul 2020 16:15:35 GMT

Great!!