cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Altonga
Frequent Visitor

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

 

Altonga_0-1670325596345.png

 

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.

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Altonga 
You can try the following

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

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @Altonga 
You can try the following

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

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

 

Power_BIEPDR.PNG

 

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

@Altonga 

Can you please explain further?

@Altonga 

Can you please explain further?

ePDR_AV_levels.PNG

 

Hi @tamerj1 , So If there was some way to use the data created from the measure you helped me with before to display on a visual somewhat to the above image? Showing the number of indiviuals who have acheived either 10%, 20% and so on. Apologise if not clear I'm having a hard time explaining it myself haha.

Hi @Altonga 

manually insert a single column table that contains the values you want to slice by, for example 0.1, 0.2, 0.3, etc.. let's call it Scores[Score]. This single column table shall remain disconnected from the main table or sny other table you have in your data model. 
now you can use the following measure in a stacked column column chart were Scores[Score] is on the x-axis and the subject is on the legend

SMX ( IF [%Trained] = SELECTEDVALUE ( Scores[Score] ), 1 )

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors