cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AoJueMing
Helper I
Helper I

customise x-axis and COUNT not SUM help please.

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.

 

 

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@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

 

 

 

 




Lima - Peru

View solution in original post

2 REPLIES 2
Vvelarde
Community Champion
Community Champion

@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

 

 

 

 




Lima - Peru

View solution in original post

Thanks very much.  Really appreciate that.

 

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

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!