Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello PowerBI gurus,
I am trying to visualise model performance metrics in line graphs so I have created 4 columns which capture the TP, FP, TN and FN predictions that the model has made.
Based on those columns I would like to calculate the Precision, Recall and F1 score.
I have tried to calculate the Recall= TP / (TP + FN ) with the following DAX formula:
However, this is not the desired output. What i am trying to achieve is :
TP / (TP + FN)
2 / (2 + 4) = 2/6 = 0.33 and at the one above it needs to be:
1 / (1 + 4) = 1/5 = 0.2 etc.
I need to do this for the precision messaure too which is: TP / ( TP + FP ) and this needs to be calculated per row level.
I have tried using SUMX too, without much success.
If anyone could help, it would be much appreciated 🙂
Solved! Go to Solution.
HI @c0ld,
Account to your description, it sounds like a cumulative calculation between two fields. Do any Index fields exist in your tables? If that is the case, you can refer to the following DAX formula to get results.
Measure:
formula =
VAR currIndex =
MAX ( Table[Index] )
VAR rollTP =
CALCULATE (
SUM ( Table[TP] ),
FILTER ( ALLSELECTED ( Table ), [Index] <= currIndex )
)
VAR rollFN =
CALCULATE (
SUM ( Table[FN] ),
FILTER ( ALLSELECTED ( Table ), [Index] <= currIndex )
)
RETURN
DIVIDE ( rollTP, rollTP + rollFN )
Calculated column:
formula =
VAR rollTP =
CALCULATE (
SUM ( Table[TP] ),
FILTER ( Table, [Index] <= EARLIER ( Table[Index] ) )
)
VAR rollFN =
CALCULATE (
SUM ( Table[FN] ),
FILTER ( Table, [Index] <= EARLIER ( Table[Index] ) )
)
RETURN
DIVIDE ( rollTP, rollTP + rollFN )
Regards,
Xiaoxin Sheng
Hi @amitchandak , thanks so much for your reply.
I have tried your suggested solution but it doesnt seem to work
@c0ld , Please try as measure and use in visual .
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi @amitchandak , thanks for your suggestion.
I have tried using a measure and that doesnt work as i cant seem to represent the change ( if the model is improving ) with a line graph. The measure just produced a single calculation ie a dot on the plot.
Here is the sample of the data:
Recall should be: TP / (TP + FN) which is calulated based on previous values:
row 1: 0 / (0+1) = 0/1 = 0
row 2: 1 / (1 + 1 ) = 1/2 = 0.5
row 3: 1/( 1 +2) = 1/3 = 0.333
TP | FP | TN | FN | Recall (expected output ) |
0 | 0 | 0 | 1 | 0 |
1 | 0 | 0 | 0 | 0.5 |
0 | 0 | 0 | 1 | 0.333 |
Thanks for your help !
HI @c0ld,
Account to your description, it sounds like a cumulative calculation between two fields. Do any Index fields exist in your tables? If that is the case, you can refer to the following DAX formula to get results.
Measure:
formula =
VAR currIndex =
MAX ( Table[Index] )
VAR rollTP =
CALCULATE (
SUM ( Table[TP] ),
FILTER ( ALLSELECTED ( Table ), [Index] <= currIndex )
)
VAR rollFN =
CALCULATE (
SUM ( Table[FN] ),
FILTER ( ALLSELECTED ( Table ), [Index] <= currIndex )
)
RETURN
DIVIDE ( rollTP, rollTP + rollFN )
Calculated column:
formula =
VAR rollTP =
CALCULATE (
SUM ( Table[TP] ),
FILTER ( Table, [Index] <= EARLIER ( Table[Index] ) )
)
VAR rollFN =
CALCULATE (
SUM ( Table[FN] ),
FILTER ( Table, [Index] <= EARLIER ( Table[Index] ) )
)
RETURN
DIVIDE ( rollTP, rollTP + rollFN )
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
This is exactly what i needed !
Thank you for taking the time to help out 🙂
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |