Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
GraceJinM
Frequent Visitor

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

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...
 
Screenshot 2023-11-08 105121.pngData Example.png
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

bchager6_0-1699471890248.png

% of Grand Total =

VAR _result =
    CALCULATE ( SUM ( Sheet1[Presenting Issues Value] ), ALL ( Sheet1[ID] ) )
RETURN
    DIVIDE ( _result, DISTINCTCOUNT ( Sheet1[ID] ) )

bchager6_2-1699472010403.png

 


 



 

View solution in original post

Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

bchager6_0-1699471890248.png

% of Grand Total =

VAR _result =
    CALCULATE ( SUM ( Sheet1[Presenting Issues Value] ), ALL ( Sheet1[ID] ) )
RETURN
    DIVIDE ( _result, DISTINCTCOUNT ( Sheet1[ID] ) )

bchager6_2-1699472010403.png

 


 



 

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? 

GraceJinM_0-1700501752139.png

 

 

Anonymous
Not applicable

@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_1-1700502384310.png

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

Thank you so much! It worked 🙂

Anonymous
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.