Forum Discussion
Percentages without null values and Dates
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.
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.