customise x-axis and COUNT not SUM help please.

[AoJueMing]

Hey

I assume this to be some pretty simple stuff and am more than capable of doing this in a number of programmes - but the method PBI demands remains a mystery to me.

I have taken some survey data.

I am trying to represent scores on a column chart.

The possible score values for a particular question are 0-10.

Nobody gave a score of (for example) 3, 5 or 7.  

When I represent the data for this question in report view, I want the x-axis to go from 0-10 (regardless of whether anyone gave a score of 3, 5, 7 - I want them to be included in the x-axis), with the y-axis displaying a COUNT for each value.  

1.  In order to display a COUNT of each value, I have made the 'score' column in my data a text column (despite this being a column of numbers).  If I have the column set as a number column when I make the chart, the values are SUM'd, with that being the value that is shown in the chart for that particular score (e.g. 4 people gave a score of 6, therefore "24" is the value for 6, instead of "4") - this is not what I want.  This is why I have made the 'score' column a text column.  Is this something a lot of people do?  Is there a better way?  It seems a bit perverse.

2.  Secondly, how do I create the column chart so that scores that received no responses in the survey are included?  The way that the data is modelled in BI means that the axis is determined by the values in the data - if you dont have a "7" in a possible answer string of 0-10 there seems to be no way to force that to build in report view?

I appreciate any help very much.  Thanks.

[Vvelarde · Accepted solution]

@AoJueMing

Hi, how are you.

Let me propose a solution:

1. Create a new table  (i named ScoreTable)

 Score (Type Number)

0

1

2

3

4

5

6

7

8

9

10

2. Related This with your Table (Score (Also type Number) - Score )

3. In your Chart (i use a Clustered Column Chart)

in Axis put Score from Score Table

In Values Score from the data Table

Click on Score (in Values) and Select Count

Finally Click on Score (in Axis) and Activate Show items with no Data-

Ready

[AoJueMing]

Thanks very much.  Really appreciate that.

It's going to take some time to learn the workings of BI, but you've certainly helped.