Issue with sumx

[Anonymous]

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,

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

[Thejeswar]

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

[Anonymous · Accepted solution]

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"

)

)

[AntrikshSharma]

Great!!

[AntrikshSharma]

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"
    )
)

[Anonymous]

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