Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Issue with sumx

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
 
 

 

 
  • Anonymous's avatar
    Anonymous
    6 years ago

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

5 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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 

       XpPrérequisWeightPoints(Rouge2)Competencies
      Value 1361210(Q(36-12,12)+1)*10=30C1
      Value 172125(Q(72-12,12)+1*5 = 25C2
      Value 1612100C3
  • Thejeswar's avatar
    Thejeswar
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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