Percentages without null values and Dates

[ponchibonos]

Hello @Irwan  and colleagues.

Some weeks ago I made this post https://community.fabric.microsoft.com/t5/Desktop/Percentages-without-null-values/m-p/4732647#M14122... about the procedure to calculate Porcentages without null values. @Irwan propouse a really good alternative.

Now I need the percentage calculations to be made taking into account only the last form that was answered.

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.

6. Takes only the latest form submission per person.

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 135, so the calculation of the percentage would have to be =(385*100)/(600-135) = 82.79%.

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

Form	Year	Topics	Questions	Percentage of each question	Percentage result
Form 1	2024	Topic 1	Question 1	20	null
Form 1	2024	Topic 1	Question 2	10	10
Form 1	2024	Topic 1	Question 3	30	0
Form 1	2024	Topic 1	Question 4	40	40
Form 1	2024	Topic 2	Question 5	40	40
Form 1	2024	Topic 2	Question 6	20	null
Form 1	2024	Topic 2	Question 7	40	40
Form 1	2024	Topic 3	Question 8	20	20
Form 1	2024	Topic 3	Question 9	15	0
Form 1	2024	Topic 3	Question 10	25	25
Form 1	2024	Topic 3	Question 11	30	null
Form 1	2024	Topic 3	Question 12	10	0
Form 2	2025	Topic 1	Question 1	20	20
Form 2	2025	Topic 1	Question 2	10	null
Form 2	2025	Topic 1	Question 3	30	30
Form 2	2025	Topic 1	Question 4	40	40
Form 2	2025	Topic 2	Question 5	40	null
Form 2	2025	Topic 2	Question 6	20	20
Form 2	2025	Topic 2	Question 7	40	40
Form 2	2025	Topic 3	Question 8	20	20
Form 2	2025	Topic 3	Question 9	15	null
Form 2	2025	Topic 3	Question 10	25	0
Form 2	2025	Topic 3	Question 11	30	30
Form 2	2025	Topic 3	Question 12	10	10
			SUM	600	385

[v-ssriganesh]

Hello @ponchibonos,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario in Power BI using sample data and created a measure that dynamically calculates the expected percentage based on slicer selection.

Here’s the DAX measure I used:

% Answered (Excluding Nulls) =

VAR _SumAnswered =

    SUM('Table'[Percentage result])

VAR _SumExpected =

    CALCULATE(

        SUM('Table'[Percentage of each Question]),

        FILTER(

            'Table',

            NOT ISBLANK('Table'[Percentage result])

        ),

        REMOVEFILTERS('Table'[Form], 'Table'[Question])                    

    )

RETURN

    DIVIDE(_SumAnswered * 100, _SumExpected)

• The numerator only includes actual responses
• The denominator remains fixed and unfiltered by slicers (to calculate true expectations)

For your reference, I’m attaching the .pbix file where you can test and validate the output and thank you, @Irwan for sharing valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Irwan]

hello @ponchibonos 

here is the result using exact same DAX as previous one.

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
)

6a. no filter

6b. Form 1 filter

6c. Question 1 filter

i am not sure what is the difference from previous post, but let us know what is your expected/desired goal.

Hope this will help.

Thank you.

[Ashish_Mathur]

Hi,

That is a lot of text.  Based on the tabe that you have shared, give a brief description and show the expected result.

[Sandip_Palit]

This one measure will perform all the necessary calculations. Just create a new measure in Power BI and paste in this code.

Important: Make sure to replace 'YourTable' with the actual name of your data table.

Adjusted Percentage =
-- This formula first identifies the latest submission for each form/person
-- and then calculates the score, excluding any questions marked as "Not applicable" (null).

VAR LatestSubmissionsTable =
ADDCOLUMNS (
'YourTable',
"IsLatest",
// This checks if the current row's year is the maximum year for that specific form.
// This effectively finds the latest submission for each form ID.
IF (
'YourTable'[FormYear] = CALCULATE ( MAX ( 'YourTable'[FormYear] ), ALLEXCEPT ( 'YourTable', 'YourTable'[Form] ) ),
1,
0
)
)
VAR FilteredToLatestOnly =
FILTER ( LatestSubmissionsTable, [IsLatest] = 1 )

-- Numerator: Sum of the actual scores from the latest forms.
VAR ActualScore =
SUMX ( FilteredToLatestOnly, 'YourTable'[Percentage result] )

-- Denominator: Sum of the potential scores, but ONLY for questions that were actually answered (not null).
VAR PotentialScore =
SUMX (
FILTER ( FilteredToLatestOnly, NOT ISBLANK ( 'YourTable'[Percentage result] ) ),
'YourTable'[Percentage of each question]
)

-- Final Calculation: Divide the actual score by the adjusted potential score.
VAR Result =
DIVIDE ( ActualScore, PotentialScore )

RETURN
Result

If this explanation and solution resolve your issue, please like and accept the solution.