topic Re: Dynamic calculating balanced accuracy for multi-class in DAX Commands and Tips

Dynamic calculating balanced accuracy for multi-class

prusik369 — Fri, 23 May 2025 09:05:21 GMT

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!

Re: Dynamic calculating balanced accuracy for multi-class

v-sdhruv — Fri, 23 May 2025 04:53:50 GMT

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

Re: Dynamic calculating balanced accuracy for multi-class

v-sdhruv — Mon, 26 May 2025 13:03:51 GMT

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

Re: Dynamic calculating balanced accuracy for multi-class

v-sdhruv — Thu, 29 May 2025 05:48:28 GMT

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

Re: Dynamic calculating balanced accuracy for multi-class

v-sdhruv — Mon, 02 Jun 2025 06:21:08 GMT

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

Re: Dynamic calculating balanced accuracy for multi-class

v-sdhruv — Thu, 05 Jun 2025 06:43:38 GMT