Exhausted all my ideas

[bibbilibobbili]

I'm lost against a problem I'm having with a data model. I think I'm going about this all wrong and need a new perspective.

I have an unpivoted column of "Results", in this column are results that need to be paired together to work out percentage reduction of measurable(weight, moisture) after processing. In the Results Column are results before processing and results after treatment, these are differentiated by Columns that describe the measurable, the date of processing and if the data point is before or after processing).  

I've found it easy to work out the mean of the % reduction, but I need to accomplish this row by row, so I can perform standard deviation on the % reduction results.

The Data looks a little something like this

Date	When	What	Result
01/10/2021	Before	Density	50
01/10/2021	Before	Weight	99
01/10/2021	Before	Moisture	20
01/10/2021	After	Density	10
01/10/2021	After	Weight	54
01/10/2021	After	Moisture	10
02/10/2021	Before	Density	60
02/10/2021	Before	Weight	91
02/10/2021	Before	Moisture	30
02/10/2021	After	Density	15
02/10/2021	After	Weight	5
02/10/2021	After	Moisture	15
03/10/2021	Before	Density	70
03/10/2021	Before	Weight	75
03/10/2021	Before	Moisture	90
03/10/2021	After	Density	20
03/10/2021	After	Weight	3
03/10/2021	After	Moisture	10
04/10/2021	Before	Density	50
04/10/2021	Before	Weight	54
04/10/2021	Before	Moisture	55
04/10/2021	After	Density	10
04/10/2021	After	Weight	12
04/10/2021	After	Moisture	51
05/10/2021	Before	Density	40
05/10/2021	Before	Weight	88
05/10/2021	Before	Moisture	45
05/10/2021	After	Density	5
05/10/2021	After	Weight	11
05/10/2021	After	Moisture	37

Thanks for reading.

[Ashish_Mathur]

Hi,

Please show the expected result.  Are you looking at a calculated column solution or a measure solution?

[v-shex-msft]

HI @bibbilibobbili,

It seems like you want to apply two calculated on your records. I'd like to suggest you create a variable table for the first calculation then add an iterator function to apply the second aggregate on the table results.

Measure Totals, The Final Word - Microsoft Power BI Community

Regards,
Xiaoxin Sheng

[bibbilibobbili · Accepted solution]

Hello again,

I managed to solve this last night. After being hit repeatedly with circular dependency errors I duplicated the query and drew in the "after" information from the 2nd query using this formula that aligned the data for me.

Column = IF('Table'[When] = "Before",
CALCULATE(FIRSTNONBLANK('Table (2)'[Result],TRUE()),
FILTER('Table (2)','Table (2)'[Date] = 'Table'[Date]),
FILTER('Table (2)','Table (2)'[What] = 'Table'[What]),
FILTER('Table','Table'[When] = "After")))

The output looked like this

And now I can perform calculations on the two columns to work out % removal on a row by row basis and perform STDEV on them.
The solution feels messy to me, I feel like there should be a way to accomplish this without duplicating the query.