Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cflook00
Regular Visitor

Percentage of unique values for chart

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%

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

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.

vyueyunzhmsft_0-1664162669236.png

 

(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:

vyueyunzhmsft_1-1664162726010.png

 

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

 

View solution in original post

4 REPLIES 4
v-yueyunzh-msft
Community Support
Community Support

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.

vyueyunzhmsft_0-1664162669236.png

 

(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:

vyueyunzhmsft_1-1664162726010.png

 

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

 

Gustavo98
Helper V
Helper V

Hi

 

Pbi example.pbix

 

Tell me if helps

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; 

in my example i created a third table too

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors