Dynamic calculating balanced accuracy for multi-class

[prusik369]

I am trying to calculate balanced accuracy as a measure but everytime   I am getting wrong results.  Solution  must take under consideration  that  number of classes is dynamic. Non dynamic way  works fine. But this is not enough good solution for me.

This is my example data after creating  the  matrix with  predictions as columns and  actuals as rows. 

			NIC_PREDICTION
		A	B	C	D	E	suma from row
	A	382	172	17	5	386	962
	B	178	292	54	14	959	1497
NIC_ACTUAL	C	64	161	64	11	1696	1996
	D	40	84	41	13	2198	2376
	E	67	197	58	20	25974	26316
sum from column	-	731	906	234	63	31213	33147

expected  results from  data above  are :

Important is that number of classes is dynamic and I am trying to do everything in one measure. The most important is result in green circle. 

For non dynamic way when classes are hardcoded dax looks like this:

not dynamic DAX =

VAR TP_A = CALCULATE(COUNTROWS('visualization_dataset'), 'visualization_dataset'[NIC_ACTUAL] = "A" && 'visualization_dataset'[NIC_PREDICTION] = "A")

VAR FN_A = CALCULATE(COUNTROWS('visualization_dataset'), 'visualization_dataset'[NIC_ACTUAL] = "A" && 'visualization_dataset'[NIC_PREDICTION] <> "A")

VAR FP_A = CALCULATE(COUNTROWS('visualization_dataset'), 'visualization_dataset'[NIC_ACTUAL] <> "A" && 'visualization_dataset'[NIC_PREDICTION] = "A")

VAR TN_A = CALCULATE(COUNTROWS('visualization_dataset'), 'visualization_dataset'[NIC_ACTUAL] <> "A" && 'visualization_dataset'[NIC_PREDICTION] <> "A")

VAR Sensitivity_A = DIVIDE(TP_A, TP_A + FN_A)

VAR Specificity_A = DIVIDE(TN_A, TN_A + FP_A)

VAR BalancedAccuracy_A = DIVIDE(Sensitivity_A + Specificity_A, 2)

VAR Result =

    "Class A: " &

    "TP = " & TP_A & ", FN = " & FN_A & ", FP = " & FP_A & ", TN = " & TN_A &

    ", Sensitivity = " & FORMAT(Sensitivity_A, "0.00%") &

    ", Specificity = " & FORMAT(Specificity_A, "0.00%") &

    ", Balanced Accuracy = " & FORMAT(BalancedAccuracy_A, "0.00%")

RETURN

    Result

Thank you in advance!

[v-sdhruv]

Hi @prusik369 ,
Just wanted to check if you had the opportunity to review the DAX provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @prusik369 ,
Just wanted to check if you had the opportunity to review the DAX provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @prusik369 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @prusik369 ,
 
Can you try:

Balanced Accuracy (Dynamic) :=
VAR Classes = VALUES('visualization_dataset'[NIC_ACTUAL])

VAR PerClassAccuracy =
ADDCOLUMNS(
Classes,
"TP", CALCULATE(COUNTROWS('visualization_dataset'), 'visualization_dataset'[NIC_ACTUAL] = EARLIER('visualization_dataset'[NIC_ACTUAL]) && 'visualization_dataset'[NIC_PREDICTION] = EARLIER('visualization_dataset'[NIC_ACTUAL])),
"FN", CALCULATE(COUNTROWS('visualization_dataset'), 'visualization_dataset'[NIC_ACTUAL] = EARLIER('visualization_dataset'[NIC_ACTUAL]) && 'visualization_dataset'[NIC_PREDICTION] <> EARLIER('visualization_dataset'[NIC_ACTUAL])),
"FP", CALCULATE(COUNTROWS('visualization_dataset'), 'visualization_dataset'[NIC_ACTUAL] <> EARLIER('visualization_dataset'[NIC_ACTUAL]) && 'visualization_dataset'[NIC_PREDICTION] = EARLIER('visualization_dataset'[NIC_ACTUAL])),
"TN", CALCULATE(COUNTROWS('visualization_dataset'), 'visualization_dataset'[NIC_ACTUAL] <> EARLIER('visualization_dataset'[NIC_ACTUAL]) && 'visualization_dataset'[NIC_PREDICTION] <> EARLIER('visualization_dataset'[NIC_ACTUAL]))
)

VAR WithScores =
ADDCOLUMNS(
PerClassAccuracy,
"Sensitivity", DIVIDE([TP], [TP] + [FN]),
"Specificity", DIVIDE([TN], [TN] + [FP]),
"BalancedAcc", DIVIDE(DIVIDE([TP], [TP] + [FN]) + DIVIDE([TN], [TN] + [FP]), 2)
)

VAR FinalResult =
AVERAGEX(WithScores, [BalancedAcc])

RETURN
FinalResult

If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi  ,

If possible, could you please provide more details about your data and your expected result ? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community