Applying a formula to a subset of rows within a table

[Anonymous]

Hello! 

I was wondering if there was a way to apply a function to only a subset of rows within a table? I would ideally like to create a report in which the user is able to select what years a specific percent reduction (greenhouse gas in this case) is starting and ending. The selection of the starting and ending date can easily be performed through the use of a parameter but I have not found a way to select  the rows of interest after this parameter was selected without applying this percent reduction rate to all of the rows within this column. 

Any tips or comments witll be helpful. Thanks! 

Austin I 

[Anonymous]

I have also created an example excel file showing exactly what I would like to do in PowerBI if that may help understand my request I am happy to share! 

[v-yuta-msft]

@Anonymous ,

Could you share some sample data and give the expected result?

Regards,

Jimmy Tao

[Anonymous]

No problem, here is a link to the box file with only 3 of the "strategies" inputted. 

You can see the bottom rows are the "inputs" and ultimately effect the totals in different years which is displayed in the visual to the right. 

Excel File

Thanks, 

Austin I 

[v-yuta-msft · Accepted solution]

@Anonymous ,

Firstly, click on column [S1], [S2] and [S3] then click "Unpivot" like below:

 

Then create three calculate columns and then drag them to table visual like below:

Start Date = CALCULATE(MIN('Table'[Year]), FILTER(ALLEXCEPT('Table', 'Table'[Season]), 'Table'[Value] <> 0))
End Date = CALCULATE(MAX('Table'[Year]), FILTER(ALLEXCEPT('Table', 'Table'[Season]), 'Table'[Value] <> 0))
Reduction = CALCULATE(MAX('Table'[Value]), FILTER(ALLEXCEPT('Table', 'Table'[Season]), 'Table'[Value] <> 0))

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.