Percentages without null values

[ponchibonos]

Hello colleagues.
I would like to know if it is possible to make a DAX that allows to do the following:
1. I have a form that several people have answered (Form 1, Form2,...).
2. The form is about three topics (Topic 1, Topic 2, Topic 3...)
3. Each form has 12 questions, of which 4 are from Topic 1, 3 from Topic 2 and 5 from Topic 3.
4. The column Percentage of each question represents the values of each question, the sum of each Topic is 100%. That is to say that all the affirmative questions of Topic 1 add up to 100% and likewise for Topic 2 and Topic 3.
5. The column “Percentage result” represents the answers given in the form, in which there is the option to answer the questions “Not applicable”, this value is reflected in the values “null”.

What I need is a DAX that allows that the “null” values are not quantified in the calculation of the percentages.

Some examples of what I need:
a) When no value is filtered, the maximum sum of the "percentage of each question" is 600, the sum of "Percentage result" is 385, I would like to calculate a percentage in which all the "null" values are not evaluated, in this case the "null" values add up to 115, so the calculation of the percentage would have to be =(385*100)/(600-115) = 79.3%.

b) If “Form 1” is filtered, the maximum value of “percentage of each question” is 300 and the sum of “Percentage result” is 175 and the “null” values are 70, so the value sought is =(175*100)/(300-70)= 76%.

c) Finally, if Question 1 is filtered the maximum value of the sum of percentage of each question is 40, the value in Percentage result is 20 and the sum of the null values is 20. So the calculation would have to be: =(20*100)/(40-20) = 100%.

I hope you can help me.

I leave the example of the table

Thank you.

[Irwan · Accepted solution]

hello @ponchibonos 

please check if this accomodate your need.

a. no filter selected. i am not sure why sum of null is 115 in your description, it should be 135 (20+20+30+10+40+15)

b. Form 1 selected

c. Question 1 selected

Create a new measure with following DAX

Value Sought =
var _Sum1 = SUM('Table'[Percentage result])
var _Sum2 = 
CALCULATE(
    SUM('Table'[Percentage of each Question]),
    FILTER(
        'Table',
        not ISBLANK('Table'[Percentage result])
    )
)
Return
DIVIDE(
    _Sum1,
    _Sum2
)

Hope this will help.

Thank you.

[ponchibonos]

Amazing!!

Works perfectly.

Thanks

[Irwan]

hello @ponchibonos 

glad to be a help.

Thank you.