Percentage of time min(value)

[mattterriault]

Below is table sheet1. I am trying to get a formula to show the percentage of time the score column is the smallest number in the set. So since Distance is filtered to "Far" , the formula would get the percentage of time each company had the lowest score

[mattterriault]

wow, that works great. Thank you. If I deselected one of the companies in a filter like, Company A, could it recompute to do the min of the ones left?

[v-zhenbw-msft · Accepted solution]

Hi @mattterriault ,

We can create one column and one measure to meet your requirement.

1. Create a calculate column to get which one is the smallest.

Column = 
var _minvalue = 
MINX(
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Size]=EARLIER('Table'[Size])
        &&'Table'[Distance]=EARLIER('Table'[Distance])),
        'Table'[score])
return
IF(
    'Table'[score] = _minvalue,1,0)

2. Then we can create a measure to get the percentage.

Measure = 
var _count = 
IF(
    ISFILTERED('Table'[Distance]),
    CALCULATE(DISTINCTCOUNT('Table'[Size]),ALLSELECTED('Table')),
    CALCULATE(DISTINCTCOUNT('Table'[Size]),ALLSELECTED('Table'))*CALCULATE(DISTINCTCOUNT('Table'[Distance]),ALLSELECTED('Table')))
return
DIVIDE(
    SUM('Table'[Column]),   
    _count)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[v-zhenbw-msft]

Hi @mattterriault ,

Sorry for that we are not clear about your issue.

How do you calculate the company A is 50%? Which row divide which row?

In Far distance, company B is 4 in size A, 1 in size B. But 1 / (1 + 4) = 20%, why company B is 50%?

And do you want to display the final result in a matrix table? The measure is in Value, the Company is in Column.

Could you provide more detail about the logic of the calculation?

Best regards,

Community Support Team _ zhenbw

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

[mattterriault]

Thanks for responding. Take a look at the view below and maybe this helps better explain. In Far distance, there are 2 sizes, A and B. Campany A was the lowest in size A and Company B in size B. So Company A is (1/2 )( 2 being the total number of sizes) or 50%  and company B was the lowest in size B or (1/2) or 50%.

If far was distance was not filtered, there would be a 4 as the denominator and company B would be 50% because it was lowest 2 of the 4 times

[mattterriault]

Sorry, mabe I am not wording well, Company B would be 50% becasue there were 2 were two seperate comparisions, size B and Size A and Company B was the min(score) on one of them with score 1 in the Size B set. Hopefully that explains better.

[Fowmy]

@mattterriault 

How do you calculate the lowest score? Can you give an example for Company B and C ?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn