Forum Discussion

ponchibonos's avatar
ponchibonos
Helper I
1 year ago
Solved

Percentages without null values and Dates

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#M1412266 about the procedure to calculate P...
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    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's avatar
ponchibonos
Helper I
1 year ago

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's avatar
    v-ssriganesh
    Community Support
    1 year ago

    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's avatar
      ponchibonos
      Helper I
      1 year ago

      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's avatar
        v-ssriganesh
        Community Support
        1 year ago

        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.