How to stop a Text field from counting

[halleleanor]

Hello All. I'm working in Power BI Desktop, and have used Get Data > Excel to pull in an Excel Table.  As part of the table, I have a column named Pie Slice that's set up as Data Type = Text, Format = Text and Default Summarization = Do Not Summarize.

I also have a column of data named Fiscal Year set up as Data Type = Whole Number, format = Whole Number, and Default Summarization = Do Not Summarize.

I'm trying to create a scatter chart with a timeline along the X Axis and rows of pie (roadmap) slice names along the Y Axis (Internal, Enterprise, Enhancements, etc.).

I have several project names that each have a pie slice name and a start date associated with them. When I press the start arrow for the scatter chart, ideally the chart would move across from the lowest Fiscal Year whole number to the highest Fiscal Year whole number, showing each project name as it scrolls (with the project name circles formatted in size and color using two other columns of data).

My issue is - every time I try to add the Pie Slice data or the Fiscal Year data to the scatter chart in either the X or Y axis, they only shows up as Count of Pie Slice / Fiscal Year. When I click on the down arrow or the elipses to the right of the data name, I don't have the option to Not Count - only to either Count or Count (Distinct). I've gone into Edit Query for both data columns, and it confirms that the Data Types are set up as Text and Whole Number respectively.

Any suggestions? I'm pretty new to Power BI and I'm not sure what to try next. Thank you in advance for any assistance you can offer!

[Anonymous]

You could try creating a Measure:

Pie Slice NoCount = FIRSTNONBLANK(#"TableName"[Pie Slice], TRUE())

and then use that measure in the scatter plot?

I use that trick for having text values display on tool tips. 

[halleleanor]

Thank you! I'll give that a try. I've been able to create a measure, so I'm hopeful I'll be able to give it a good test.

I've added the new measure but when I add it to the Y Axis it's still showing values of 0 through 10 instead of the text values, so I'm thinking I still haven't gotten it quite right.

[v-sihou-msft]

@halleleanor

The scatter chart is to display points at the intersection of an x and y numerical value, combining these values into single data points. Both X and Y axis should be numeric field (like measures). If you put a category field, it will aggregate values with count function. This is the expected behavior of scatter chart. Generally, we should put the category field into Legend in scatter chart. For more details, please see: Tutorial: scatter charts and bubble charts in Power BI

In your scenario, I suggest you try other visual like "Line + Column" chart which can have timeline on X-axis and slice on Lengend.

Regards,

[halleleanor]

Thank you for the information! That makes sense. I'll give your other suggestion a try - I didn't know I could have a timeline in the Line + Column chart.