How to removing null values from % of Grant Total visualizations?

[GraceJinM]

I have a muti-select item that I have unpivoted into attribute/value form (Example of the data at the bottom). The goal is to visualize the % of times each presenting issue was selected across all of the people in our sample. The issue is that our percentages are completely off (they should be significantly higher based on previous excel reports using the same data source) because the null values are being included in the percentage calculations (at least this is what I think is happening- please let me know if I am incorrect in this assumption).

Things I have tried:

• Adding a filter to the visual to only include values that are not blank. This did nothing?
• Creating a new measure that doesn't include the null values with the DAX expressions below. When visualized, both still showed the same messed up visualization as before.
  • GrandTotalWithoutNulls = SUMX(FILTER('Presenting Issue source', 'Presenting Issue source'[Presenting Issues Value] <> BLANK()), 'Presenting Issue source'[Presenting Issues Value])
  • GrandTotalWithoutNulls2 = CALCULATE(SUM('Presenting Issue source'[Presenting Issues Value]), NOT ISBLANK('Presenting Issue source'[Presenting Issues Value]))
• Removing all the rows that have null values in column A. To me, this seems like it would fix our visualization issues, as the only items left in the data should be the ones we want to account for; however, each way I've tried to do this does not work.
  • Filtering the data to only include non-null values. This did not influence the visualization
  • Use the "remove blank rows" function. This did not even remove any blanks.

Any help at all is much appreciated. Also please let me know if I have simply gotten this all wrong and need to completely recalibrate...

[Anonymous · Accepted solution]

@GraceJinM  Let me know if this works for you. Below is sample data, the measure I wrote, and the results.

% of Grand Total =

VAR _result =

    CALCULATE ( SUM ( Sheet1[Presenting Issues Value] ), ALL ( Sheet1[ID] ) )

RETURN

    DIVIDE ( _result, DISTINCTCOUNT ( Sheet1[ID] ) )

[Anonymous · Accepted solution]

@GraceJinM  Let me know if this works for you. Below is sample data, the measure I wrote, and the results.

% of Grand Total =

VAR _result =

    CALCULATE ( SUM ( Sheet1[Presenting Issues Value] ), ALL ( Sheet1[ID] ) )

RETURN

    DIVIDE ( _result, DISTINCTCOUNT ( Sheet1[ID] ) )

[GraceJinM]

New Issue: When I apply fliters the individual percentages on the visualization go over 100%? It is okay for the total percentage to equal > 100%, but the individual issues should be < 100%. Any idea how to fix this? 

[Anonymous]

@GraceJinMModifying the expression to look something like this should work:

VAR _result =

    CALCULATE ( SUM ( Sheet1[Presenting Issues Value] ), ALL ( Sheet1[ID] ) )
VAR _result2 = 

    DIVIDE ( _result, DISTINCTCOUNT ( Sheet1[ID] ) )

RETURN
IF ( _result2 >= 1, 1, _result2)

[GraceJinM]

Additional example of the data. We are flitering by location. 

[GraceJinM]

Thank you so much! It worked 🙂

[Anonymous · Accepted solution]

@GraceJinM  Great! I'm glad I could help. Can you please mark my response as the solution?