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

[ponchibonos]

Hello, colleagues. Apologies for not responding sooner; I was ill and unable to do so.
Thank you for all your responses. I believe my explanation of what I need was unclear.

I already have the DAX that calculates the percentages without evaluating the null values. What I need is a measure, in addition to the previous calculation, to only calculate the most recent response when there are two responses from the same person. In the example from my model, Person 1 responded in 2024 and 2025, so I need the measure to only calculate for the year 2025 for the Person 1.

Here is the pbix file

Percentages and Dates 

Thanks in advanced.

[v-ssriganesh]

Hello @ponchibonos,
I’ve reviewed your requirement and was able to reproduce it successfully. I adjusted the DAX measure so it calculates % Answered (excluding nulls) but only for the most recent year per person when multiple years of responses exist.

Key changes made:

• The calculation now filters to each person’s latest year.
• Null values are still excluded from the calculation, as in your original logic.
• Result is correct at 52.94% for your sample dataset.

For your reference, I’ve attached the updated .pbix file so you can review the changes and logic directly.

Best regards,
Ganesh Singamshetty.

[ponchibonos]

Thanks for your answer is almost perfect. The only thing I haven't been able to do is display the value for person 1 when I filter for the year 2024. The legend “Blank” appears. Is there a way to modify the DAX so that when filtering for years other than the last ones, the value of the measure for that year appears?

[v-ssriganesh]

Hello @ponchibonos,

I’ve reproduced your scenario in Power BI and noticed the reason why seeing a blank result for 2024 is that your existing measure is hard-coded to return only the latest year per person. When you filter to a year that isn’t the latest for a given person, no rows match that condition, so the calculation returns blank.

To fix this, I’ve modified the measure so it works dynamically:

% Answered (Excluding Nulls) =

VAR _SelectedYearCount =

    CALCULATE(

        DISTINCTCOUNT('Table'[Year]),

        ALLSELECTED('Table'[Year])

    )



VAR _MaxYearPerPerson =

    CALCULATE(

        MAX('Table'[Year]),

        ALLEXCEPT('Table', 'Table'[Form])

    )



VAR _SumAnswered =

    SUM('Table'[Percentage result])



VAR _SumExpected =

    CALCULATE(

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

        FILTER(

            'Table',

            NOT ISBLANK('Table'[Percentage result])

        )

    )



RETURN

IF(

    _SelectedYearCount = 1,  
    DIVIDE(_SumAnswered * 100, _SumExpected),

    CALCULATE(

        DIVIDE(_SumAnswered * 100, _SumExpected),

        FILTER('Table', 'Table'[Year] = _MaxYearPerPerson)

    )

)

For your reference, I’m attaching the .pbix file.

Best regards,
Ganesh Singamshetty.

[ponchibonos]

Thanks again for your time and your answer. 

I'm still having trouble getting the measurement to do what I need it to do.

In this latest version you sent me (% Answered August 13th), it works exactly the same as the original version (% Answered original) in that it considers all the data regardless of whether there is data from more recent years.

If you don't use any filters, the measure value is the same as the original.

If you use a filter for Person 1, it calculates the data for both 2024 and 2025.

The "% Answered August 9th" version already calculated just the values for the most recent year. The problem with it was that if you selected a year that was not the most recent with the filter, it appeared as “Blank.”

I left the pbix with the three versions of DAX 

https://drive.google.com/file/d/1hMxu2FzfSDYhR_24DVIEXL4tobxbEIua/view?usp=sharing

I hope you can help me adjust the DAX so that it can solve what I need.

Thank you very much.

[v-ssriganesh]

Hello @ponchibonos,
Thanks for sharing those examples.

I have reproduced your scenario in Power BI Desktop and confirmed that the output matches your expected results:

For your reference, I’m attaching the .pbix file containing the working example so you can review the setup and DAX logic in detail.

Best Regards,
Ganesh singamshetty.

[ponchibonos]

Thanks again for this new version. 

The DAX has a detail that I have not been able to identify that makes the calculation inaccurate.

When filtering for the year 2025. The result should be:
Sum of percentage result = 90
Divided by the total excluding null values = 170

For a result of 52.94%

With the latest version you sent me, the result is 54.17%

The DAX works fine with the Person 1, Person 2 filter.

Could you help me find the error?

I left the pbix with the four versions of DAX 

https://drive.google.com/file/d/1hMxu2FzfSDYhR_24DVIEXL4tobxbEIua/view?usp=sharing

Thanks again.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[v-ssriganesh]

Hello @ponchibonos,
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[v-ssriganesh]

Hello @ponchibonos,

Hope everything’s going great on your end. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @ponchibonos,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

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