Forum Discussion

DaxBoi's avatar
DaxBoi
New Member
5 years ago

Conditional SUM

I have a dataset that looks like the following. 

For each ID there is an Attribute which has a score. There is also a target 0-4 with a weighting.

 

I'm looking to create a visual with a differential view of the weighted target score and actual score.

 

My plan is to have columns to calculate these values, however I'm struggling to formulate the DAX for the columns after the ~ symbol. 

I've tried Calculate/SUMX/Filters but can't get these to work as expected. Any help is much appreciated

 

IDAttributeScoreTargetWeighting~Sum(Target*Weight)Diff
1A1400.2~2.6+1.4
1A1410.2~2.6+1.4
1A1420~2.6+1.4
1A1430~2.6+1.4
1A1440.6~2.6+1.4
1A2.........~......
Need Help

5 Replies

  • DaxBoi's avatar
    DaxBoi
    New Member
    5 years ago

    For clarity, I'm looking to sum based on a number of attributes - such as ID, and Attribute. And then do a second calculation (finding the differential) based on the same attributes 

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    DaxBoi , As measures

     

    new score = sumx(All(Table),[Target]*[Weighting])

    or
    new score = sumx(Allselected(Table),[Target]*[Weighting])
    or
    new score = calculate(sumx(Table,[Target]*[Weighting]),All(Table))

    or

    new score = calculate(sumx(Table,[Target]*[Weighting]),Allselected(Table))

    diff = Sum(Table[Score]) -[new score]

     

    As new column

    new score = sumx(Table,[Target]*[Weighting]

    diff = [Score] -[new score] //Both are column here

    • DaxBoi's avatar
      DaxBoi
      New Member
      5 years ago

      I think I wasn't clear in my question

       

      The aim is to have a conditional sum that looks like this. The sum would be over the 5 total rows for


      ID = 1 Attribute = A1

       

      and then another sum over the rows for

       

      ID = 1 Attribute = A2

       

      and so on.

       

      IDAttributeScoreTargetSum(T*W)Diff
      1A14(0-4)2.6+1.4
      +4rows asabove   
      1A23(0-4)3.5-0.5
      +4rows    
      1A34(0-4)1+3.0
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    DaxBoi  - You said:

    "For clarity, I'm looking to sum based on a number of attributes - such as ID, and Attribute. And then do a second calculation (finding the differential) based on the same attributes"

     

    PBIX is attached under sig, Table (4)

     

    So, 2 columns:

    Sum Target Weight = 
    VAR __Table = 
        ADDCOLUMNS(
            FILTER('Table (4)',[ID]=EARLIER([ID]) && [Attribute]=EARLIER([Attribute])),
            "Product",[Target]*[Weighting]
        )
RETURN
    SUMX(__Table,[Product])


Diff = 
    VAR __Table = 
        ADDCOLUMNS(
            FILTER('Table (4)',[ID]=EARLIER([ID]) && [Attribute]=EARLIER([Attribute])),
            "Product",[Score]*[Weighting]
        )
RETURN
    SUMX(__Table,[Product]) - [Column]

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi DaxBoi 

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your data model and your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou