Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
prusik369
New Member

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    
  ABCDEsuma from row
 A382172175386962
 B17829254149591497
NIC_ACTUALC64161641116961996
 D4084411321982376
 E6719758202597426316
sum from column-731906234633121333147

 

expected  results from  data above  are :

prusik369_0-1747990456603.png

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!

1 ACCEPTED SOLUTION
v-sdhruv
Community Support
Community Support

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


View solution in original post

5 REPLIES 5
v-sdhruv
Community Support
Community Support

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
Community Support

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
Community Support

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
Community Support

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
Community Support

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.