Forum Discussion

Altonga's avatar
Altonga
Frequent Visitor
3 years ago
Solved

Multiple row and Column Add up (Remove Duplicates)

I am currently working on a project using a grading system and want to display total% of an individuals skillset. The grading system works using the below image (I've only used my own test ID but in the data set it is showing all 930).

 

 

Scenario 1 = 10%, Scenario 2 = 20%, Scenario 3 = 30% and Task 1 + Task 2 both give 20% each resulting in the individual being 100% trained. The main issue with this is that the user can have multiple entries for each header, resulting in the total% being over 100% in some cases.

 

Is there a measure that can be implemented to just look at each of these columns and just take one entry from each and ignore duplicates while adding up to a max of 100%? Happy to give more details.

DAX
rows
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Altonga 
    You can try the following

    %Trained =
MAX ( 'Table'[Scenario1%] ) + MAX ( 'Table'[Scenario2%] )
    + MAX ( 'Table'[Scenario3%] )
    + MAX ( 'Table'[Task1%] )
    + MAX ( 'Table'[Task1%] )

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Altonga 
    You can try the following

    %Trained =
MAX ( 'Table'[Scenario1%] ) + MAX ( 'Table'[Scenario2%] )
    + MAX ( 'Table'[Scenario3%] )
    + MAX ( 'Table'[Task1%] )
    + MAX ( 'Table'[Task1%] )
    • Altonga's avatar
      Altonga
      Frequent Visitor
      3 years ago

      Hi tamerj1, 

       

      Thank you so much for your reply, this worked perfectly! I have noticed that it has affected one of my other visuals which I was showing a top level view for the training records. Please see below. (The top visual.)

       

       

      Is there a way to make the data from the previous solution work in such a view?