Sum of distinct values - New Column

[ccjj]

Hi Community, 

I have a dataset with the following table.  I am trying to create a new column based on Count of distinct "Company" and distinct "Services" (divided by total services of 10).  I found a way to do this as a Measure, but can't seem to get it to work as a Column.  Any thoughts/help is appreciated.

[VahidDM]

Hi @ccjj 

Try this code to add a new Column:

Column = 
VAR _A =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Services] ),
        ALLEXCEPT ( 'Table', 'Table'[Company] )
    )
RETURN
    ( 10 / _A ) / 100

Output:

Download the sample file (Attached).

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[ccjj]

Thank you!  

The first 3 shows is showing 3.33% when it should show 33%. Any way to adjust that?

[VahidDM]

@ccjj 

Can you please add more details how did you calculate 33% and 10%?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[ccjj]

"Company1" has 3 distinct services, so the calculation is 3/10 = 30% (sorry I did the math wrong :))

"Company2" has 2 services but only only 1 distinct, so the calculation is 1/10 = 10%

[VahidDM · Accepted solution]

Now it's solved 🙂

Try this code:

Column = 
VAR _A =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Services] ),
        ALLEXCEPT ( 'Table', 'Table'[Company] )
    )
RETURN
_A/10

And set the column format as a Percentage, output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[ccjj]

that worked perfectly...THANK YOU!!!