Do a Divide function

[epang0714]

Hi, 

I have a combined table with actual studetns vs Targets. The two fields come from a separate table. Is it possible to do a divide function as a calculated column to see porportion of target achieved? Thanks

	actual students	targets
Uni A	1000	800
Uni B	1500	1200
Uni C	2000	2500

[govind_021]

Hi @epang0714 
try creating a table with summarizecolumns function , 
SUMMARIZECOLUMNS( ) DAX function.
and then you can use divide

[epang0714]

the original actual students is a measure as following

CALCULATE(DISTINCTCOUNT('Fact data'[StudentID]),FILTER('Fact data','Fact data'[Timeperiod]= 'Fact data'[Commencement_Year]

I cannot find the field in the function. 

[Anonymous · Accepted solution]

Hi @epang0714 ,

You can create calculated column as below in the table 'Target data' to get it:

Porportion =
VAR _actual =
    CALCULATE (
        DISTINCTCOUNT ( 'Fact data'[StudentID] ),
        FILTER (
            'Fact data',
            'Fact data'[Uni] = 'Target data'[Uni]
                && 'Fact data'[Commencement_Year] = 'Fact data'[Timeperiod]
        )
    )
RETURN
    DIVIDE ( _actual, 'Target data'[targets] )

Best Regards

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a measure.

# target students: = 
SUM( target[targets] )

# actual students: = 
SUM( actual[actual students] )

Ratio: = 
DIVIDE( [# actual students:], [# target students:] )