Forum Discussion
Tablular data to calculate quality accuracy
I need a support in both of power query and DAX in the below and highlight to me which scenario is the best to use in terms of performance and simplicity.
I have a tabular data where each index represents transaction evaluation against specific attributes, and I need to calculate quality accuracy per severity as follow
EU = 100%-(count of scores /unique index) considering the below condition:
count of scores per index where severity is EU and incase there is more than score 1 within the same transaction index, it must be counted only one (by other means, if any transaction index with the severity of EU failed (score 1), should reflect only one per this index)
The same goes for BC and CC
For NC, it should be calculated on attribute level
NC=100%-(count of score 1 /unique attribute per each index) considering the below condition:
count of scores per attribute where severity is NC and incase there is more than score 1 within the same attribute and same index, it should be count only 1 ((by other means, if any transaction index with the severity of EU with the same attribute failed (score 1), should reflect only one per this index)
Hi Anonymous ,
Please try below steps:
1. beow is my test table, according to your descriptions to create
Table:
2. create two measure and add it to table visual
EC = VAR cur_trans = SELECTEDVALUE ( 'Table'[Transaction] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Transaction] = cur_trans && 'Table'[Score] = 1 && 'Table'[Severity] = "EU" ) VAR tmp1 = SUMMARIZE ( tmp, [Transaction], "Avg for Score", AVERAGE ( [Score] ) ) RETURN COUNTROWS ( tmp1 )NC = VAR cur_trans = SELECTEDVALUE ( 'Table'[Transaction] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Transaction] = cur_trans && 'Table'[Score] = 1 && 'Table'[Severity] = "NC" ) VAR tmp1 = SUMMARIZE ( tmp, [Advisor ID], [Attribute], "Avg for Score", AVERAGE ( [Score] ) ) RETURN COUNTROWS ( tmp1 )Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
daXtremeSolution Sage
Hi Anonymous
Great, but could you please give a simplified picture of what you want to do but still representative? It would really be nice if you could trim down the data set to what's essential and show the calculations "in slow motion," so to speak. Then it'll be easier for us to assist you. For the time being, the description is not the easiest to understand.
Thanks a lot.
daXtreme thanks for trying to support me and it's my bad to explain. i tried to make it simple as much as i can.
the below is the data that i need to calculate from to produce the 2nd table with a certain condidtions
Table Data
This is advisor evaluation score card definition
Transaction: transaction number
Advisor ID : advisor ref. number in system
Severity : severity of scored evaluation
Attribute : score evaluation category
Sub=attribute : score evaluation sub-category
Score : 1 means fail, and blank means Pass
I just need to extract the below table or to be a measure so i can use it per any dimension (date, agent...)
Conditions
With the below considerations
EC : whenever there is failed attribute within the same transaction, it should be calculated as one regarding the number of failed attributes
NC : it is the opposite, it counted based on attributes level so whenever there is failed attributes, it should be counted. But if advisor failed 2 sub-attributes in the same attribute, it should be counted as 1.
Hi Anonymous ,
I want to confirm some information with you.
1. You want to get table 2 from table1, is right?
2. could you give me more detailed descriptions about how to calculate for "EC" and "NC"
for below descriptions, i cannot understand the calculate logic absolutely, could you please give me a calculate sample?
Thanks for your efforts & time in advance.
Best regards,
Community Support Team_ Binbin YuHi Anonymous ,
Please try below steps:
1. beow is my test table, according to your descriptions to create
Table:
2. create two measure and add it to table visual
EC = VAR cur_trans = SELECTEDVALUE ( 'Table'[Transaction] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Transaction] = cur_trans && 'Table'[Score] = 1 && 'Table'[Severity] = "EU" ) VAR tmp1 = SUMMARIZE ( tmp, [Transaction], "Avg for Score", AVERAGE ( [Score] ) ) RETURN COUNTROWS ( tmp1 )NC = VAR cur_trans = SELECTEDVALUE ( 'Table'[Transaction] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Transaction] = cur_trans && 'Table'[Score] = 1 && 'Table'[Severity] = "NC" ) VAR tmp1 = SUMMARIZE ( tmp, [Advisor ID], [Attribute], "Avg for Score", AVERAGE ( [Score] ) ) RETURN COUNTROWS ( tmp1 )Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.