Measure column - summarization

[dart73]

Hello guys,






I have a matrix with some measure columns that creates some conditions. That´s the Delta_classification.






Delta_classification = if([DL_TPH_Delta]>0,5;"CRITICAL";if([DL_TPH_Delta]>0,3;"BAD";if([DL_TPH_Delta]>0,2;"REGULAR";if([DL_TPH_Delta]>0,1;"ACCEPTABLE";if([DL_TPH_Delta]>0,05;"GOOD";"EXCELLENT")))))


Now I want to summarize it by category CRITICAL, BAD, REGULAR, ACCEPTABLE, GOOD, EXCELLENT but as this is a measure I"m having trouble to do it. Could you kindly help me on this?!






BR

 

[Col Delane]

Hi BR and welcome to the Forum

Firstly, sorry about how this looks - the result of dodgy copying & pasting from Excel!!!

Secondly, nesting so many IF functions together to return the appropriate classification is not good practice. A better option is a central lookup table like the following:

A B C 2 Table Name: tblDeltaClass 3 DL TPH Delta Classification 4 - Excelent 5 0.05 Good 6 0.10 Acceptable 7 0.20 Regular 8 0.30 Bad 9 0.50 Critical Table Name: tblData C15 = VLOOKUP( B15, tblDeltaClass, 2, TRUE ) 14 ID DL TPH Delta 15 0.02 Excelent 16 0.05 Good 17 0.10 Acceptable 18 0.20 Regular 19 0.14 Acceptable 20 0.24 Regular 21 0.34 Bad 22 0.54 Critical 23 0.08 Good 24 0.22 Regular <tbody> </tbody>

<tbody>
</tbody>

Now, what do you mean by "summarise it by category"?

To COUNT the number of entries by category, set up another table like this:

	Table Name: tblSummary
	C30 = COUNTIFS( $C$15:$C$24, B30 )
29	Classification	Count
30	Excelent	1
31	Good	2
32	Acceptable	2
33	Regular	3
34	Bad	1
35	Critical	1
36	Total	10

<tbody>
</tbody>

Alternatively, if your data has a much greater number of entries, the better option may be to create a Pivot Table where you can choose any one of several measures, or perform full statistical analysis (standard deviations, etc.).