Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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
Hi @epang0714
try creating a table with summarizecolumns function ,
SUMMARIZECOLUMNS( ) DAX function.
and then you can use divide
the original actual students is a measure as following
I cannot find the field in the function.
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
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:] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
53 |