topic Re: How to calculate percentage of each group out of total number of distinct rows in DAX Commands and Tips

How to calculate percentage of each group out of total number of distinct rows

EtoiledeMO — Tue, 03 Dec 2024 03:37:12 GMT

Hello!

I have a table that looking at type of insurances that customers have and customers could select more than one type, however ultimately, I would like to create a visual in my dashboard that shows the percentage of responses for each insurance type OVER the DISTINCT number of respondents. For example, if 301 people selected 'Medicaid' (this includes folks who selected only Medicaid or selected Medicaid and other types of insurance, for the numerator all I care about is that they selected 'Medicaid') out of 861 DISTINCT individuals who responded to the survey, how do I get a percentage that reflects non-distinct count per group OVER a distinct number of IDs?

Here is my visual thus far:

^The percentages you see at the bottom of each square in the tree map DO represent what I am looking for, for example 301/861 folks= 34.96% selected Medicare, however I had calculated that in R before exporting to Excel and then importing into Power BI. I want to calculate this however IN Power BI if possible instead because when I try to filter based on age, educational level, etc. as seen on the right-side of the screen, those percentages do not change.

Here is my table that the visual is based on- column Q1= ID of individual, column insurance= insurance type, total_insurance= distinct count of Q1 (I calculated this in R) & count_insurance=count of insurance by group /total_insurance, again calculated in R. Any suggestions for how to calculcate total_insurance & count_insurance in PowerBI rather than bringing them over from R would be much appreciated, thank you! 😊

How do I recreate these last two columns exactly as they are but using a calculation made in PowerBI rather than R?^

Attached is a PowerBI workbook at this link in Dropbox https://www.dropbox.com/scl/fi/fmtzurp0k6jkl36bk7qll/insurance_long.pbix?rlkey=i6ph2ji5ll9hwicv14yjtcg5q&st=upw9xiot&dl=0 .

The table in my screenshot above is for the insurance_long table in the PowerBI workbook. The sheet_na table in the PowerBI workbook contains the ID "Q1" and the age category variable used as a filter on the far right of the visual "Q4_cat" and then there is also a table for race as that also serves as one of the filters. Thank you!

Re: How to calculate percentage of each group out of total number of distinct rows

lbendlin — Mon, 02 Dec 2024 22:21:47 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: How to calculate percentage of each group out of total number of distinct rows

EtoiledeMO — Tue, 03 Dec 2024 03:38:30 GMT

Thank you! I have now added a Dropbox link to my PowerBI workbook and some clarifying sentences.

Re: How to calculate percentage of each group out of total number of distinct rows

lbendlin — Tue, 03 Dec 2024 20:50:11 GMT

I have cleaned up your data model, and removed the extra columns. Your result can be achieved with a measure, but you will need to accept that it doesn't add up to 100%

Using the implicit Percentage of Grand Total would be the better option.