cancel
Showing results for
Did you mean:
Regular Visitor

Show As a Percentage of Unique Values in a Column

Hi everyone,

This is the structure of my data:

Since I needed all Substances in one columns, in order to derive statistics, I had to unpivot 4 Substances columns:

When I derive statistics about substances using Clastered Bar Chart, and show Value as Percent of grand total I get next results (when I exclude 'NA's which stands for Not Available)

This is OK, since this calculates substances as a percent of all substances, after excluding NAs. But this is not what I need. I need each substance to be shown as a percent of Names column. Since columns are unpivoted, calculations base on Picture 2 in this post are not accurate. The results that I should get are:

So total number of X is 3, divided by total number of names (3), and we get 100%. Total number of Y is 2, divided by total number of names (3), and we get 0.67.

I tried using Quick Mesaure to divide Count of Substances by Count of distinct names, but I don't get accurate results.

How I can solve this?

Thank you.

6 REPLIES 6
Super User

Re: Show As a Percentage of Unique Values in a Column

You probably need a denominator like:

```Denominator =
VAR __table = ALL('Table'[Name])
VAR __table1 = DISTINCT(__table)
RETURN
COUNTROWS(__table1)```

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Regular Visitor

Re: Show As a Percentage of Unique Values in a Column

This works, but there is a problem with filtering.

So in the denominator I only need to have some values that are filterer by another column. Let me try to explain:

If this is my data:

I only need distinct values in 'Name' column that have 'L' status.

I run your code and it works:

But when I filter only Names that have 'L' status, tha value of denominator stays the same (3) and I got next result:

But here the value of denominator should be 2.

Regular Visitor

Re: Show As a Percentage of Unique Values in a Column

I also want denominator to change when I filter names by other columns. If I calculate it in proposed way, then the value of denominator is fixed.

Super User

Re: Show As a Percentage of Unique Values in a Column

Couple different things you can do.

```Denominator =
VAR __table = FILTER(ALL('Table'[Name]),[Status]="L")
VAR __table1 = DISTINCT(__table)
RETURN
COUNTROWS(__table1)```

Or:

```Denominator =
VAR __table = SELECTCOLUMNS(ALLEXCEPT('Table',[Status]),"__name",[Name])
VAR __table1 = DISTINCT(__table)
RETURN
COUNTROWS(__table1)```

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Regular Visitor

Re: Show As a Percentage of Unique Values in a Column

I'm still getting incorrect results.

As I said, I have other columns that I need to filter with, and the value of the denominator do not change.

Let me augment my table. To make this simpler, only status now is 'L' so, there is no need to filter table by status, but I'll have to filter by 'Age Group' and 'Year'.

 Name Status Age Group Year Substance 1 Substance 2 Substance 3 Substance 4 A L 0-20 2010 X Y Z NA B L 0-20 2010 X Y NA NA C L 21-35 2011 X NA NA NA D L 21-35 2011 Y NA NA NA E L 36-50 2012 X Y NA NA F L 36-50 2012 Z X NA NA

Once again, the goal should be to create a visual that will show the percentage of substances that were involved in deaths of individuals that are presented in 'Name' column. So, this table is about individuals who overdoses from using substances presented in 4 Substance columns. To count the total number of different substances I unpivoted these four columns.

 A L 0-20 2010 Substance 1 X A L 0-20 2010 Substance 2 Y A L 0-20 2010 Substance 3 Z A L 0-20 2010 Substance 4 NA B L 0-20 2010 Substance 1 X B L 0-20 2010 Substance 2 Y B L 0-20 2010 Substance 3 NA B L 0-20 2010 Substance 4 NA C L 21-35 2011 Substance 1 X C L 21-35 2011 Substance 2 NA C L 21-35 2011 Substance 3 NA C L 21-35 2011 Substance 4 NA D L 21-35 2011 Substance 1 Y D L 21-35 2011 Substance 2 NA D L 21-35 2011 Substance 3 NA D L 21-35 2011 Substance 4 NA E L 36-50 2012 Substance 1 X E L 36-50 2012 Substance 2 Y E L 36-50 2012 Substance 3 NA E L 36-50 2012 Substance 4 NA F L 36-50 2012 Substance 1 Z F L 36-50 2012 Substance 2 X F L 36-50 2012 Substance 3 NA F L 36-50 2012 Substance 4 NA

When shown in absoulte number, the things are easy. Withoud 'NA's the visuals is:

Now, I need these number to be shown as percentges of individuals in the name column. The result should be this:

 X 83.3% Y 66.7% Z 33.3%

Also, these number should reflect the 'Age Group' and 'Year' filters applied. So, If I want to see in how many deaths substances  were involved in year 2010, the result should be:

 Name Status Age Group Year Substance 1 Substance 2 Substance 3 Substance 4 A L 0-20 2010 X Y Z NA B L 0-20 2010 X Y NA NA

 X 100% Y 100% Z 50%

I do not get these results when I apply suggested codes, simply because the value of denominator do not change when I apply filters.

For example, when I apply this code:

```Denominator =
VAR __table = ALL(Sheet2[Name])
VAR __table1 = DISTINCT(__table)
RETURN
COUNTROWS(__table1)```

And then add quick measure to divide Values in unpivoted column by Denominator, I get correct initial result:

But when I choose only year 2010, then I get:

Whic doesn't correspond to the results that I should get. The value of denominator stays the same (6), and it should be (2) when filtered by year 2010.

Please let me know if you need some additional information.

Thank you!

Regular Visitor

Re: Show As a Percentage of Unique Values in a Column

Furthermore,

I tried to set denomintor using next code:

```Denominator =
VAR __table1 = DISTINCTCOUNT(Sheet2[Name])
RETURN
__table1```

Now the denominator is responsive to filters, but he Quick Measure (Value/Denominator) doesn't show the correct resuls. Please see the image below.

Visual 'Counte of Value by Value' is good, just as Denominator. But visual 'Count of Value Divided by Denominator by Value' doesn't show expected results.