Showing percentage according to values in column

[ahsanlhcgov]

I have a small problem in showing percentages. 

I have a two colums which has Values

opportunities and Oppo_stage

I would like to show a percentage of opportunities 'lost'.

oppo_stage can only be won and lost.

Can someone please help me with that.

[tringuyenminh92 · Accepted solution]

Hi @ahsanlhcgov,

Please try like this, if it's not like what you expect, please kindly show me your sample data and i will suggest another approach

% Lost = DIVIDE(CALCULATE(COUNTROWS(Data),filter(ALL(data),Data[Oppo_Stage]="Lost")),CALCULATE(COUNTROWS(Data),ALL(Data)))

We will count all rows which is Lost and divive with all rows. In case you have additional filter by another column, you could use replace ALL by ALLEXCEPT method

If this works for you please accept it as solution and also like to give KUDOS.

[tringuyenminh92 · Accepted solution]

Hi @ahsanlhcgov,

Please try like this, if it's not like what you expect, please kindly show me your sample data and i will suggest another approach

% Lost = DIVIDE(CALCULATE(COUNTROWS(Data),filter(ALL(data),Data[Oppo_Stage]="Lost")),CALCULATE(COUNTROWS(Data),ALL(Data)))

We will count all rows which is Lost and divive with all rows. In case you have additional filter by another column, you could use replace ALL by ALLEXCEPT method

If this works for you please accept it as solution and also like to give KUDOS.

[ahsanlhcgov]

Hi, 

i want to show the percentage of 'LOST' opportunities

[ahsanlhcgov]

also the values need to be distinct. the value 1146 is not distint. if I select distint it will show the value 1

[Vvelarde · Accepted solution]

hello @ahsanlhcgov

create a measure to count the opportunities loses

Opp-Lost-Count =
CALCULATE (
    DISTINCTCOUNT ( Opportunities[Opportunityid] ,;
    FILTER ( Opportunities, Opportunities[Stage] = "Lost" )
)

 Create a measure to divide by all opportunities

%Opp_lost =
DIVIDE (
    Opportunities[Opp-Lost-Count],
    DISTINCTCOUNT ( Opportunities[Opportunityid] )
)

[ahsanlhcgov]

brilliant. what I ended up doing is creating 3 measures but your solution was the basis for that. thanks alot for that.

[ahsanlhcgov]