Adding a value defaults to count instead of the actual value.

[Anonymous]

Every month I download a dataset from partner center about our client usage, it is a simple 7 columns most are percentages shown as decimals, one for whole numbers and a column for client names. The data doesn't even need to be transformed or manipulated really, aside for changing the format from decimal to percentage, and even that is optional. When I went to do the same thing this month I added the table to my master.pbix to compare month to month change, when I add one of the percentage values to a cluster column chart that measures the same metric for each new month, the value was added to axis, when I dragged it down to the value section it converts it to "Count of Skype/Teams" and all the entries are over 100% on the visual graph.

From my research of the problem I have noticed a few things that are odd, first the values in the table even though they are all numerical they are not showing the epsilon icon next to them. I have read that the reason it is defaulting to "Count" is because Power Bi is marking them as text, even though the data type shows "decimal number", and format is set to percentage. The default summarization is set to "Don't summarize".

In the past everything has defaulted and been correc automatically and I havent changed my process, the older months show the little epsilon and default summary is set to "Sum" and this has never effected the visuals. When I load the data into its own instance of power bi under a new .pbix it loads correctly and the data is recognized as number and not text, but I need the data to load into my master .pbix to be able to compare it side by side. 

Any help would be great. 

[JarroVGIT]

Might be a localization issue by the sound of it. Is it possible that you used to import '0.01' values and that are now being imported as '0 COMMA 01' (0,01) values? Is the dataset based on CSV or Excel? Might be helpful to open the last dataset and the secondlast dataset side by side to compare. What happens if you force the datatype of decimal number in the Power Query Editor?

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

The files was a TSV, this was my bad I usually open them and save them as xslx. So after doing that and re-importing into Bi the values now have epsilons next to them, indicating a numerical value, and also automatically drop into the value section of the visualization pane, but they are still showing max values (100%) because it is summarizing them with Sum. All of the other past values have the default Sum but don't do this. If I change it to do not summarize then the epsilon goes away and the value changes to "Sum of skype/Teams" and shows the same maxed out results. 

I also double checked to make sure they weren't commas and are in fact decmials.

[v-chuncz-msft]

@Anonymous 

In the Visualizations pane, right-click the field and change the aggregation. To my mind, this is more reliable.

[Anonymous]

Im assuming you mean changing from 'sum' to 'count', 'average', 'median', ect... all of these seem to have a similar affect in that they change all of the tables values rather than showing them individually if that makes sense. In the photo the purple lines are the value that I am having the issues with, the other are from past months.

[JarroVGIT]

By the looks of it there is something wrong with the data format you have loaded in. The columns are in the wrong order perhaps before you appended the query?

[Anonymous]

They are in decending order based on the last months numbers, the smaller orange bars. When I look at them in data view I see this

But when I load them into the report view for any graph it gives them all the same value visually based on 'sum' 'average' 'max 'min' ect... It wants to summarize all the data to the same number, it's so strange. I have loaded and reloaded the data in a few different times and ways and continues to do the same thing, but if I load them into their own .pbix instance they appear to function normally. 

[JarroVGIT]

Your problem seems to be at another level, if I look at your legend. It legends as dates except for the last one, indicating you are adding a different value column to the visual? Please share a screenshot of entire screen while the visual is selected to confirm, thanks

[Anonymous]

I can see how that appears confusing, I am renaming the dates after I add them to the graphic, they all start as Skype/Teams, I have not changed the last one because I have added and removed it so many times I haven't bothered to edit for final appearances.

Here are some screen shots to show you what im working with.

The first two show the values I am working with, the first is a month that is working correctly, it defaults to sum but this doesn't seem to affect the outcome on the visual. The second is the newest data it's set to sum but it seems to actually take the sum of the whole column and input it for each individual value. Pictures 3 and 4 are what happens when I added the newest data.

 

[Anonymous · Accepted solution]

I seemed to have fixed the error, though I am not entirely sure what I did. It had to do with the table relationships, I connected the customer names and tenant names with a 1 to 1 relationship between all of the tables in use, and it corrected itself to display individual values.

Thank you all for your help, without your suggestions I wouldn't have questioned certain aspects of the problem and might not have come to the solution until much later. 

[JarroVGIT]

Good to hear! Have a great new year 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂