Tablular data to calculate quality accuracy

[Anonymous]

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)

[Anonymous]

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 Yu

[Anonymous]

@Anonymous  thanks for your support

EC calculation = 1-(failed EC / total transactions)  if one attribute failed within the same transaction, it should be considered as only one

NC calculation = 1-( Failed NC / total transactions) 

[Anonymous · Accepted solution]

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.

[Anonymous]

can anyone support please

[Anonymous]

@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.

[daXtreme]

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.