topic Need help on how to filter and divide column for a percentage in DAX Commands and Tips

Need help on how to filter and divide column for a percentage

NewLearner555 — Tue, 13 Feb 2024 06:22:48 GMT

Hello,

Normally when I do a table for a percentage I use the following formula to get a percentage.

measure= DIVIDE(COUNT('Q2'[Black.Vancover]),CALCULATE(COUNT('Q2[Black.Vancover]),ALLSELECTED()))

The example table is below:

Table would be called for this example Q2

ID Marker	Do you like milk?	Black.Vancover
100	Yes	1
101	No	null
10564	Yes	null
1687	Yes	null
10006	No	1
6804	Yes	1

This table is created by merging two tables together using the unique ID Marker. In the example respondants who were Black and in Vancover was merged with the table with the question and answers. This allows me to make measures with the formula above to get a percentage to use in graphs. I compare the different groups together by making seperate tables and the relationship between them is the answers. (I make a seperate table ex below, that has a relationship with the different tables for the different groups I am comparing in a graph)

Order	Answer
1	Yes
2	No

Now my problem is I have a question that I need to graph that is essential 4 questions being asked.

The example below only is showing two questions, but what I am trying to do, is somehow in a measure filter the information so I am only getting a percentage for the question "Do you like milk", instead of a percentage for both "Do you like milk" and "Do you like cheese?"

ID Marker	Question	Answer	Black.Vancover	South Asian.Vancover
100	Do you like milk?	Yes	null	1
101	Do you like cheese?	No	1	null
102	Do you like cheese?	No	null	1
103	Do you like cheese?	Yes	1	null
104	Do you like milk?	Yes	1	null

Does anyone know a solution to this problem? I cannot figure out the filter function with this formula.

Re: Need help on how to filter and divide column for a percentage

lbendlin — Tue, 13 Feb 2024 01:07:57 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 or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Need help on how to filter and divide column for a percentage

Anonymous — Tue, 13 Feb 2024 03:58:47 GMT

Hi NewLearner555

I've known your issue, maybe you can try filter function:

I create a set of sample data:

Then create a calculate column:

Column = IF( 'Table (2)'[week]=1, CALCULATE( COUNTROWS('Table (2)'), FILTER( ALLSELECTED('Table (2)'), 'Table (2)'[week]=1 ) ) )

The result is as follow:

^{Best Regards,}

^{Zhengdong Xu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

Re: Need help on how to filter and divide column for a percentage

NewLearner555 — Tue, 13 Feb 2024 06:24:18 GMT

I changed the information in the screenshot I provided so there was no sensitive data, but I've fixed the post like in the link you provided and hopefully made the problem more clear.

Re: Need help on how to filter and divide column for a percentage

NewLearner555 — Tue, 13 Feb 2024 06:37:24 GMT

When I tried this calculation but it returns as an error. The If statement is asking for a measure instead of the columns in my table.

I did edit my question and made some example tables.

Re: Need help on how to filter and divide column for a percentage

lbendlin — Tue, 13 Feb 2024 22:02:49 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.