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 ABCDEsuma from row A382172175386962 B17829254149591497NIC_ACTUALC64161641116961996 D4084411321982376 E6719758202597426316sum from column-731906234633121333147 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 ResultThank you in advance!

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]) RETURNFinalResultIf 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

prusik369

New Member

1 year ago

Solved

Dynamic calculating balanced accuracy for multi-class

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
1 year ago
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

5 Replies

v-sdhruv
Community Support
1 year ago
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
v-sdhruv
Community Support
1 year ago
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
Community Support
1 year ago
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
Community Support
1 year ago
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
Community Support
1 year ago
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

Forum Discussion

Dynamic calculating balanced accuracy for multi-class

5 Replies

Related content

Inventory and Stock balance calculation

Balanced accuracy for multi-class

Projected Available Balance Dax Calculation Help

Daily Balance Calculation

Calculating closing balance