Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bapaknyaanak2
Regular Visitor

How to show value instead of aggregate

Hello,

 

I've been searcing how to show value in visualization using line chart, instead of aggregate (sum/count/ average, etc). I've set in data field modelling to "do not summarize" but it changed to "count". Any help appreciated.

1 ACCEPTED SOLUTION

@v-haibl-msft

@LaurentCouartou

 

Thanks for replying my post.

 

I think i've found how to make Power BI shows the actual value, instead of aggregate in line chart. In my case, it's solved by creating a slicer, and i just need to drag the column that I interested in seeing it as an actual value to it. Then, my line chart will show the actual value.

 

In @v-haibl-msft's example, he can make it like that because it only has one value and once the X-axis data appear twice in the column (which is my case), then power bi started to show it as aggregate.

 

Thanks for your help guys.

View solution in original post

14 REPLIES 14
LadyRobot
Frequent Visitor

Hello, I am in very similar situation. I have pulled data actually from SQL database.

Setting is "Do not summarize" for each column which I am working with.

I am trying to archive to get data on daily basis as there is query in SQL for max and min values for every day.

How can I change aggregation of those values to single value as it is in the database?

LadyRobot_0-1665056438849.png

 @Bapaknyaanak2 

 

marktrinidad05
New Member

I wonder also how he did it as he just said by using a slicer solved his problem.

Anyway here is my scernario:
I want to display the original value on the line chart in Power BI  but it just gave me a count.

Here is my solution:
Though to my own problem I used the SUM in field values of the line chart and place date on the axis and used a slicer for date and for customer this enabled me to see the value I want to display but take note make sure you only have one value per date per customer to get just one value or so in my own scenario.

Hope this helps.

chrissyk
New Member

I still don't get how to solve this question? When I click slicer, my other chart disappears. Any help appreciated 🙂

rohitbinnani
Advocate III
Advocate III

If you go to the DATA tab and click on the column name of the table and change the data type of that particular column from Text to Whole Number/Decimal Number and to the number of value you want.

  Capture 3.JPG

Thanks and Regards,

Rohit Binnani.

 

 

In line chart, there are 2 columns. Date and Currency, For each date, I need to see currency value, however, its aggregating. I have changed the data type to number in Modelling and selected Unsummarized option, however, it still is showing count.. and giving other aggregating options. Any Help about how to solve this.

Creating a Datetable from the Azure Marketplace (Free) (Extended CalendarEnglish) is the best option as so you can work and display dates using that as your axis value and not from your data table.

 

You can use the FORMAT Dax function to change how the date is displayed, etc so it looks and feels while working and sorting in the appropriate manner.

v-haibl-msft
Employee
Employee

@Bapaknyaanak2

 

What is the type of your column? Please make sure it is numeric but not text. If issue persists, please provide your PBIX file to me if possible.

 

How to show value instead of aggregate_1.jpg

 

Best Regards,

Herbert

@v-haibl-msft

 

Ok, I know how to set to date now, but it seems does still does not show the exact value that i wanted. It's aggreagated. How did you do your example ? Did you change the X or Y settings ?

In the example above, there is only one value for each date.

 

In this kind of chart, however, only one Y is expected for each X. That is why you can only get aggregated values. It is up to you to decide which aggregation, though.

 

Are you looking for a scatter plot?

@v-haibl-msft

@LaurentCouartou

 

Thanks for replying my post.

 

I think i've found how to make Power BI shows the actual value, instead of aggregate in line chart. In my case, it's solved by creating a slicer, and i just need to drag the column that I interested in seeing it as an actual value to it. Then, my line chart will show the actual value.

 

In @v-haibl-msft's example, he can make it like that because it only has one value and once the X-axis data appear twice in the column (which is my case), then power bi started to show it as aggregate.

 

Thanks for your help guys.

Anonymous
Not applicable

@Bapaknyaanak2 could you please tell me what you mean by "i just need to drag the column that I interested in seeing it as an actual value to it. Then, my line chart will show the actual value."

which column are you refering to here ? I am also facing an issue where i have to show value without any aggregation...

@Anonymousshow me what you have done, so i could explain details..
Anonymous
Not applicable

@Bapaknyaanak2

1. i am trying to analyse price.. so in the data it is possible that a customer is sold the same product at different prices over the period of time based on the quantity purchased (higher the quantity lower the price). I want to identify these different prices

2. The data also have return records e.g.

15th Oct Product A sold      $10

19th Oct Product A Return -$10.

3. Below is whats happening...

When i am using the netprice as a measure it is summing the entries in the raw data. Below are the images.

PowerBI...
Image.png


Raw Data in excel...
Raw Price.png
However when i changed the data type of netprice to text and use it in the visual it gives me count and only options i get is CountD and Count.


Image2.png

Herbert,

 

How did you do it ? This is exactly i wanted. The type of my column already in number. Btw, how do you change the sales by date in your example ? I only have option changing it to day. How can i send you the file. Below you can see my screenshoot.

 

Capture.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.