Calculate percentages with filters and ponderate values

[ponchibonos]

Hi community.

I have a table to which I want to calculate percentages depending on the filters I apply. The conditions for the filters are the columns “Nombre de la Organización, “Atributo”, “Ficha peguntas.EAS”. The value on which I want the percentage to be calculated is the column “Valor ponderado”.
The two characteristics that I have not been able to resolve are:
Not all records in “Valor ponderado” have the same values but can be 0,1, 2 or 3 so a simple formula of adding the values and dividing by the number of values does not work for me to calculate the percentage.
The other point is that there are records that have no response (null) so they should not be included in the percentage calculation.

I would like to know how to calculate the percentages to consider the records with different values (0, 1, 2 or 3) and not to consider the null values for the calculation.

The result would have to be that when you filter by “Nombre de la Organización, “Atributo”, “Ficha peguntas.EAS” the percentages are calculated.

Thank you very much.

Regards

Table with data 

[Sahir_Maharaj]

Hello @ponchibonos,

Can you please try this approach:

Percentage Measure = 
VAR FilteredValues =
    FILTER(
        TableName,
        NOT ISBLANK(TableName[Valor ponderado])
    )
VAR TotalSum =
    SUMX(FilteredValues, TableName[Valor ponderado])
VAR TotalCount =
    COUNTAX(FilteredValues, TableName[Valor ponderado])
RETURN
    DIVIDE(TotalSum, TotalCount, 0)

[ponchibonos]

Thanks for your answer @Sahir_Maharaj 

With some small modifications to your proposal I managed to solve the first problem I had, that of calculating the percentages considering only the cells with values.

However, I have not been able to solve the second problem which is a formula to calculate the percentage considering the weighting of the answers.

 Some cases to clarify the idea based on the next table:

Case 1. I make a filter to select by “# Question” and select question 5.

The result of the percentage formula above is 200 because (2+2)*100 / 2 = 200

Case 2. I make a filter to select by “Organization” and select “2”.

The result of the above percentage formula is 100 because (0+2+1+0+2)*100 / 5 = 100

However, there are two questions that had a value of “0”.

It should be noted that for each question there can only be three answers: null, 0 and depending on the weighting (1, 2 and 3). In other words, a question with a weighting of 3 could not have values of 1 or 2.

Thanks for your help.

Regards

[Anonymous]

Hi @ponchibonos 

What do you mean by this phrase: "a question with a weighting of 3 could not have values of 1 or 2".

Regarding the two cases you mentioned, what would the results be after applying the percentage calculation formula that takes into account the weight of the answers? Could you elaborate on this in detail so that I can better assist you?

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ponchibonos]

Thanks for your answer @Anonymous 

What I mean is that each question has three possible values. For example, question "1" can be null, 0, and 1. Question "5" can be null, 0, and 2. This difference is because I consider question "5" to be more important than question "1".

[Anonymous]

Hi @ponchibonos 

My understanding is that for the second example you mentioned above, the result you want is (0+2+1+0+2)*100 / 3 = 166.67 instead of (0+2+1+0+2)*100 / 5 = 100. Is my understanding correct? If I am mistaken, please point it out.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ponchibonos]

In the case you mention.

I want is (0+2+1+0+2)*100 / 7  = 71.42 instead of (0+2+1+0+2)*100 / 5 = 100

7 Is because:

first register can be null, 0 or 1

Second register can be null, 0 or 2

Third register can be null, 0 or 1

Forth register can be null, 0 or 1

Fifth register can be null, 0 or 2.

That means the 100% would be 7 

Thanks in advance

[Anonymous · Accepted solution]

Hi @ponchibonos 

Based on your needs, I have created the following table.

Then, you can ues the following measure to get the result you want:

Weight = IF(SELECTEDVALUE(Consultal[Valor Ponderado]) = 0 , 1 ,SELECTEDVALUE(Consultal[Valor Ponderado]))

Percentage = 
VAR FilteredValues =
    FILTER(
        'Consultal',
        NOT ISBLANK('Consultal'[Valor ponderado])
    )
VAR TotalSum =
    SUMX(FilteredValues, 'Consultal'[Valor ponderado])
VAR TotalCount =
    SUMX(FilteredValues, [Weight])
RETURN
    DIVIDE(TotalSum*100, TotalCount, 0)

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sahir_Maharaj]

Excellent - anytime, happy to help!