Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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:
Thank you in advance!
Solved! Go to 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
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
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
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
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
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
User | Count |
---|---|
14 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
21 | |
11 | |
10 | |
10 | |
8 |