Values Over 100 % should be dispaly as 100%

[sdhn]

Hi All,

I am using following mesaure to calculate Deployed% of products.

 Deployed % = DIVIDE ('Table'[NAD],[TED])

NAD & TED are simple mesaures and retriving values from columns. 

NAD = SUM(Table[Product Agent Deployed])

TED = SUM('Table'[Total points])

On graph I used Product Names and above mesure to display.

One departmnet is showing 101 %.

We do not want to show anyting more than 100 %.

Any number over 100 % should be dispaly as 100 %.

What to add in the code?  Your help will be appreciated. 

Thanks 

[Fowmy · Accepted solution]

@sdhn 

Use the following measure:

Deployed % = MIN( DIVIDE ('Table'[NAD],[TED]) , 1 )

[sdhn]

Deployed % = MIN( DIVIDE ('Tenable'[NAD],[TED],1)

OR

Deployed % = MIN( DIVIDE ('Tenable'[NAD],[TED],1))

The MIN function only accepts a column reference as an argument.

[Fowmy]

@sdhn 

Hope you are creating a MEASURE with the formula that I shared, I tested and it works. You may share a dummy file with this scenario.

[sdhn]

NED & TED ar ealso measue retriving values of columns as below:

NAD = SUM(Table[ Agent Deployed])

TED = SUM('Table'[Total points])

[Anonymous]

Hi @sdhn ,

I think min(...,1) will work. Here I have a test in my sample. NED & TED are both measures and it works well.

NAD = SUM('Table'[Product Agent Deployed])

TED = SUM('Table'[Total points])

Deployed % = MIN(DIVIDE ([NAD],[TED]),1)

Result is as below.

Best Regards,
Rico Zhou

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

[sdhn]

message as:

The MIN function only accepts a column reference as an argument.

[sdhn]

I removed "DIVIDE" from the code.  Code validated but all values become 100 %.

I want to display 100 % when its over 100 % or equal to 100 %.  Thanks 

Deployed % =

VAR _Ratio =

(

SUM('Table'[Product Agent Deployed])+ SUM('Table'[Total points])

)

RETURN

MIN ( _Ratio, 1 )

[sdhn]

I wrote this but getting errors:

Deployed % =

VAR _Ratio =

DIVIDE (

SUM('Table'[Product Agent Deployed])+ SUM('Table'[Total points])

)

RETURN

MIN ( _Ratio, 1 )

ERROR:

Too few arguments were passed to the DIVIDE function. The minimum argument count for the function is 2.