Measure that will show Percentage of distinct rows per ID

[BobJohnson]

Hi All,

Can you help me figure out how to create a measure that will show % of specific Rating per ID. 

I have multiple rows of the same ID with different Ratings, i need to find out the percentage of each individual rating per ID.

I will merge ID with seqence numbers as they should be treated as separate. 

Let's use the top ID 00001-1-1 as an example. It has one amber and one green rating. I would like to know a way of showing that this ID has 50% Red and 50% Green rating and so on.

As a note I will only be interested in Amber and Red ratings. Is there a way to show a table with just one row per ID, with columns Red and Amber shown as percentage? 

Thanks in advance,

Bob

[BobJohnson · Accepted solution]

Hi Stephen, 

It mostly works however since there are multiple lines per ID it only shows what percentage of rating is per that line. 

So in this instance we have 3 ID's.

As long as filter is set for all ratings, ID 00001 shows 50%green and 50% amber. When I filter to just Amber it shows id 00001 as 100% amber.

Is there anyhting that could show all of the ID's that for instance have over 20% red?

If i filter with the current measure it will only highlight the lines with rating Red and shows it as 100%. 

Thanks,

Bob

[v-stephen-msft]

Hi @BobJohnson ,

Here's the measure.

Percentage = var _count1=CALCULATE(COUNT('Table'[ID]),FILTER(ALLSELECTED('Table'),[ID]=MAX('Table'[ID])))
var _count2=CALCULATE(COUNT('Table'[Rating]),FILTER(ALLSELECTED('Table'),[Rating]=MAX('Table'[Rating])&&[ID]=MAX('Table'[ID])))
return DIVIDE(_count2,_count1)

You can set up filters for the visual as follows.

Best Regards,

Stephen Tao

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

[BobJohnson · Accepted solution]

Hi Stephen, 

It mostly works however since there are multiple lines per ID it only shows what percentage of rating is per that line. 

So in this instance we have 3 ID's.

As long as filter is set for all ratings, ID 00001 shows 50%green and 50% amber. When I filter to just Amber it shows id 00001 as 100% amber.

Is there anyhting that could show all of the ID's that for instance have over 20% red?

If i filter with the current measure it will only highlight the lines with rating Red and shows it as 100%. 

Thanks,

Bob

[v-stephen-msft · Accepted solution]

Hi @BobJohnson ,

I am sorry for my negligence and based on my research, in the measure I provided earlier, ALLSELECTED should be replaced with ALL. Because ALLSELECTED preserves the filtering of filters, ALL ignores all filters.

Percentage = var _count1=CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),[ID]=MAX('Table'[ID])))
var _count2=CALCULATE(COUNT('Table'[Rating]),FILTER(ALL('Table'),[Rating]=MAX('Table'[Rating])&&[ID]=MAX('Table'[ID])))
return DIVIDE(_count2,_count1)

Then this should be the best practice at the moment, and it seems more difficult for a row of ID to show the percentage of red and amber.

Best Regards,

Stephen Tao

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

[BobJohnson]

Thanks for reply Stephen,

Perfect, it works.

Regards, Bob