Using parameters in calculated columns

Question

I'm a bit confused here. I hope you can help me with this problem.For a housing association I want to make a forecast of the rental income for the next five years.I have to calculate this individually for each year and for each house because of company policies and state regulations. So I have to use calculated columns instead of measurements. Correct me if I'm wrong.For example: if the rent of a house is lower than roughly 60% of the maximum rent of that specific house, then the rent must increase more than if the rent is 80% of the maximum rent.And this check should be done every year.I have included a number of parameters for the rent increase in my report so that the housing association can see what the effect will be on total income.As far as I know you can't use parameters in calculated columns in PowerBi.Is there a work around I can use?

greg_deckler · Answer

JeroenN&nbsp;Correct, parameters (what if parameters) will not affect calculated columns. You will need to use a measure. You should be able to use a measure it will just be a bit more complicated and you will have to deal with measure totals most likely.
&nbsp;
To go further, would need sample data.&nbsp;Sorry, having trouble following, can you post sample data as text and expected output?Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490The most important parts are:1. Sample data as text, use the table tool in the editing bar2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.

jeroenn · Answer

This is a small example of the data I use.House_IdCurrentRentMaximumRent%RentP0001840105080%P000264265298%P0003548102653%P0004849117872%P000542867663%P000657885468%P000763785475%P0008560104654%P0009601117651%P001041068460%P0011870102785%P0012668119956%P001371496874%P001447293351%P0015687114360%P0016814120068%P001765768596%P001866572292%P001944274759%&nbsp;And the parameters on the report:Parameters&nbsp;Rent &lt; 60% MaxRent4,00%Rent 60 - 75% MaxRent3,00%Rent 75 - 90% MaxRent2,00%Rent &gt; 90% MaxRent0,00%&nbsp;And the result should be:House_IdRent Year 1Rent Year 2Rent Year 3Rent Year 4Rent Year 5P0001856,80873,94891,41909,24927,43P0002642,00642,00642,00642,00642,00P0003569,92592,72616,43634,92653,97P0004874,47900,70918,72937,09955,83P0005440,84454,07467,69481,72496,17P0006595,34613,20631,60650,54663,55P0007656,11669,23682,62696,27710,19P0008582,40605,70629,92648,82668,29P0009625,04650,04676,04703,08731,21P0010426,40439,19452,37465,94479,92P0011887,40905,15923,25941,72941,72P0012694,72722,51744,18766,51789,50P0013735,42750,13765,13780,43796,04P0014490,88510,52530,94552,17574,26P0015707,61728,84750,70773,22796,42P0016838,42863,57889,48916,16934,49P0017657,00657,00657,00657,00657,00P0018665,00665,00665,00665,00665,00P0019459,68473,47487,67502,30517,37&nbsp;And of course: totals by city, province and country.&nbsp;So each year the rent is based on the rent in the previous year. In formula:Rent Year i = Rent Year i-1 * ( 1 + a )Where a is based on Rent Year i-1 / Maximum Rent and one of the four parameters.

Forum Discussion

Related content

Make Parameter effective using calculated column

Converting calculated column into parameter

TMDL - calculated column

Direct Lake Model - Calculated Column/Tables

Reordering Column Headers in Matrix with Field Parameters and Calculation Groups