Create a custom measure

[mmoroni]

Looking for help on a measure column

I currently have 2 columns in a BI report.  I would like to get a calculation to show the "take rate" by doing a custom measure to take the "distinct count" of account number (current customers) DIVIDED BY # of units potential.  I want to display this as a %.

IE, if I have 7 distinct account numbers, and iut has # of potentials to be 14, I want a result of 50%

10 distinct account num,bers with 100 potential should equal 10%.

'MDU BI Elations'[Account Number]     (must be a distintc count)

'MDU LOCATIONS Smart Sheet'[# of units potential]

[v-yingjl · Accepted solution]

Hi @mmoroni ,

If you have each column for each table, you can create a measure like this:

RE = 
DISTINCTCOUNT ( 'MDU BI Elations'[Account Number] )
    / SUM ( 'MDU LOCATIONS'['# of units potential] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mmoroni]

That did the trick.  I had something similiar (maybe even teh same), but had results as "infinity".  Now that I look at iut, if I have SOMETHING divided by nothing, it would toss up an odd answer.

Thank you!

[vanessafvg]

you could try something like this

% of units potential =
VAR accounts =
    CALCULATE ( DISTINCTCOUNT ( table[accountnumber] ) )
RETURN
    FORMAT ( DIVIDE ( accounts, unitsofpotential, 0 ), "Percent" )

[mmoroni]

Not quite letting me.

% of units potential =

VAR accounts =

CALCULATE ( DISTINCTCOUNT ('MDU BI Elations'[Account Number]))

RETURN

FORMAT ( DIVIDE (accounts, 7,0), "Percent" )
The "7" is where I am trying to enter, 'MDU LOCATIONS Smart Sheet'[# of units potential]  But it doesnt allow.

[vanessafvg]

ok so these are sitting in different tables? you need to share some sample data in text format please.

I would need to seee both tables.

also do your tables have a relationship between them?