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.



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.

ponchibonos's avatar
ponchibonos
Helper I
1 year ago

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

      I made some modifications to the proposal of v-ssriganesh . It now works perfectly to meet the calculation requirements.

       

      % Respuesta (Año Máximo y Filtro por Persona) =
      VAR _SelectedYears = VALUES('Hoja1 (2)'[Year])
      VAR _MaxYear = MAX('Hoja1 (2)'[Year]) // Solo el año máximo seleccionado

      VAR _SumAnswered =
      CALCULATE(
      SUM('Hoja1 (2)'[Percentage result]),
      KEEPFILTERS('Hoja1 (2)'[Year] = _MaxYear), // Respeta filtros externos (como Person)
      REMOVEFILTERS('Hoja1 (2)'[Year]) // Elimina conflicto con ALLSELECTED
      )

      VAR _SumExpected =
      CALCULATE(
      SUM('Hoja1 (2)'[Percentage of each question]),
      KEEPFILTERS('Hoja1 (2)'[Year] = _MaxYear),
      KEEPFILTERS(NOT ISBLANK('Hoja1 (2)'[Percentage result])),
      REMOVEFILTERS('Hoja1 (2)'[Year])
      )

      RETURN
      DIVIDE(_SumAnswered, _SumExpected, 0) * 100

       

       

      Thank you all.

       

       

    • ponchibonos's avatar
      ponchibonos
      Helper I
      1 year ago

      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.