how to use Cell referencing to calculate a column

[Anonymous]

Hello Experts, @Albert , 

Could you please advise me.

I have a dataset which is stored in dataverse and am performing write back operation using the power app. Everyting is working fine but in one column I need to calculate the value which is the some percentage of the preious stage.  As shown in the below table.

Stages	next Stage (%)	Volume (per stage)
A	70% 0r 0.7	1000 ( this is input value or use can change this value
B	40 % or 0.4	700 (70% of 1000)
C	20% or 0.2	280 (40% of 700)
D		56 (20% of 280)

As Volume column is a caclulate columnn in which fist value is default value or user input, and the rest values are depends on the previous stages percentage.

Please help how can I create this column using measure or if statement or using any other Dax formula. 

I would really apreciate your help. 

[Anonymous]

Hi @Jihwan_Kim ,

thanks for this solution, I'm wondering instead of haiving a parameter column, is it possible that user can enter any value. Which is not defined in volume column. for exmaple if user wants to enter the volume around 5000.

then

I need your suggestion on it.

[Jihwan_Kim]

Hi, 

I am not sure if there is a way to input values and see the result without having a parameter table.

Perhaps, if users can see the source data, then users can change the value in the source and refresh it every time, but I believe this is not a good practice. 

[Anonymous]

Hi 

I want to share a file with you but I am not able to  could you please tell me how can I share.

In the mean time I need some advise.

I have created three coditional columns using 9 parameters as shown in the image. names are= exported-stage, sent to next stage, and sold to customer.Now I wanted to create a fourth calculated column using the 10th parameter named volume whose value is 10000 by defualt. as you can see in the screenshot.

now I want to calculate the same thing which  I previously discussed that is , i

if stage =harvest  then volume = 10000 (parameter value)

if stage = primary processing  then volume = 10% *10000(paramter value)

if stage=further processing = then volume = 40%* (10% of 10000) which is 1000.

exactly the same you solved previously.

I would really appreciate your thoughts on it.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Volume CC =
VAR _inputvalue = 1000
VAR _currentstage = Data[Stages]
VAR _untilprevious =
    SELECTCOLUMNS (
        FILTER ( Data, Data[Stages] < _currentstage ),
        "@percentage", Data[next Stage (%)]
    )
RETURN
    IF (
        Data[Stages] = "A",
        _inputvalue,
        _inputvalue * PRODUCTX ( _untilprevious, [@percentage] )
    )

[Anonymous]

Hi @Jihwan_Kim ,

Thanks for your prompt reply.
However, could you guide how end user can change the value, instead of changing it from formula. 

is there any possiblity we can enter the value through parameter on the report page or any other way?

Infact am using power app to enter the value or to write back in the datasource. as it is a calculated coulmn and first value is input value which user can change from power app. 

[Jihwan_Kim]

Hi,

I am not sure whether I understood your question correctly, but in case if users want to input the value by themselves, I suggest creating a measure instead of calcualted column rather than let users to change the source.

Please check the below picture and the attached pbix file.

Volume measure =
VAR _inputvalue =
    SELECTEDVALUE ( Parameter[Parameter] )
VAR _currentstage =
    MAX ( Data[Stages] )
VAR _untilprevious =
    FILTER ( ALL ( Data ), Data[Stages] < _currentstage )
RETURN
    SWITCH (
        TRUE (),
        MAX ( Data[Stages] ) = "A", _inputvalue,
        _inputvalue * PRODUCTX ( _untilprevious, Data[next Stage (%)] )
    )