Percentage of unique values for chart

[cflook00]

I feel this is an easy one but I can't figure it out.

I have two Tables, both with column Name. Table 1 has a full data set of names which can repeate, Table 2 has a subset of names which can repeat and are not all inclusive of the names in Table 1

Table 1

Name

John

John

John

Amanda

Peter

Kristin

Kristin

Sam

Table 2

Name

John

Sam

Kristn

Kristin

Amanda

I have some charts with Table1 'Name' across the X axis and I would like to show the percentage of counts of the name appears in Table 2 compared to the full data set in Table 1

for example, in my small example here, John would appear on the X-Axis first and the line would be 1/3=33%; Amanda would be next and would be 1/1=100%, Peter next and be 0/1=0%

[v-yueyunzh-msft · Accepted solution]

Hi, @cflook00 

According to your description, you want to get the number of names appearing in Table2 as a percentage of the number of names appearing in Table1. Right?

Here are the steps you can follow:

(1) My test data is the same as you.The two table don't need to build relationships.

(2)We can click "New measure" to create a measure :

Percentage = DIVIDE(CALCULATE( COUNT('Table2'[Name])+0 , 'Table2'[Name] = SELECTEDVALUE('Table1'[Name])),COUNT(Table1[Name]))

(3)Then we can put the 'Table1'[Name] and the measure in the visual you want and we can meet your need. The result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi

Pbi example.pbix

Tell me if helps

[cflook00]

Hey thanks for taking a look at this one for me; unfortunately i can't download anything to my work computer; i think i found a long workaround -- creating a third table with Name, distinct values, then linking Table 1 and Tabl 2 to it, using 'relatedtable' function & using this Table3[name] in the charts --- was hoping it would be as simple as a single dax statement so i wouldn't have to manage a 3rd table; 

[Anonymous]

in my example i created a third table too