Table counting 0s as well as 1s

[katie15013]

Hi,

I want to create a table visual that shows counts of particular health conditions. The data I am using currently codes this information as Y or N, so I have created custom columns for each condition and used an IF formula to change the Ys into 1s. This seems to have worked fine. But when I put these columns into my table it just seems to be counting all rows instead of only those with 1s in.

How can I also get this to show one health condition per row (and the counts in the 2nd column) rather than each condition by column?

[Daviejoe · Accepted solution]

Would it not be better to create Measures for each of the columns?

Autism Count =

Sum (Autism_Count)

[katie15013]

Thanks! I couldn't figure out how to do as you said (I used that formula but it didn't seem to work for me. Can you tell I'm a beginner? 🙄) but I managed to figure out a way based on your suggestion:

And have this now! So thanks !

[Daviejoe]

p.s. if you feel that my answer gave you the correct solution then please select the Accept as Solution button.

Thanks

David

[Daviejoe]

Hey Katie,

no worries, no one was born an expert so we all start from the same place.

Glad you could find a solution.

Regards

David

[ibarrau]

Hi. Can you show in excel or paint your expected result? I'm not sure if you are looking for avoid summarization to show the rows clicking on the down arrow of the field in the visualization like this:

I also think you might be looking for showing the values by rows instead of columns changed in matrix format menu under values:

Hope this helps, if it doesn't please show your expected result 🙂

[katie15013]

Sorry for replying over two messages.

I switched to rows using the matrix visual. It is still counting strangely though. There are definitely not 7 1's for each of those cateogories.

[ibarrau]

No worries. That sounds like something you should check out in your data and your count calculation. It's not a problem of changing to rows :S

Power Bi is only reading the data. Try creating a filter with those 0 and 1 category counting cards or something like that to be sure that the problem might be in the data.

Regards,

[katie15013]

Hi, thanks for your reply. When I click 'Don't Summarise' it now shows the results like this:

I would just like to show it like this:

[ibarrau]

Ok. Like I said in the second part of the post, that result would be found only in a Matrix visualization. Forget about "Don't Summarize", that was for another purpose. Return to your original visualizaiton. Make the visualization a matrix. Go to format menú > values option > Show on Rows. Once you enable that option you will see the data like that.

Regards,