Forum Discussion
Create a custom measure
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%.
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.
5 Replies
you could try something like this
% of units potential =
VAR accounts =
CALCULATE ( DISTINCTCOUNT ( table[accountnumber] ) )
RETURN
FORMAT ( DIVIDE ( accounts, unitsofpotential, 0 ), "Percent" )Not quite letting me.
% of units potential =VAR accounts =CALCULATE ( DISTINCTCOUNT ('MDU BI Elations'[Account Number]))RETURNFORMAT ( 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.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?
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.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!
