Line and Clustered Column Chart - Points above Bars

Question

Hi,&nbsp;I've created a Line and Clustered Column Chart as shown here. The data is held on our IBM AS/400 as I am doing a Direct Query in PowerBI to get this data:The bars are a count of Orders within that transaction status at the current moment in time and only shows data for today.The points are the total value of the Orders within that transaction status and only shows data for today.&nbsp;&nbsp;&nbsp;My Axes are setup like so:&nbsp;My question is, how do I get the points to display on top of each respective bar and then have a Line that's running from one point to another. For example:I'd like the point with the value of 5.0K to show above the bar that shows 25.I'd like the point with the value of 10.9K to show above the bar that shows 193 - Which it currently does.&nbsp;I'd like the point with the value of 6.7K to show above the bar that shows 105.&nbsp;Is this possible as I can't seem to figure it out?&nbsp;Thanks for any help.&nbsp;

anonymous · Answer

Hi&nbsp;AhmedT001&nbsp;,
&nbsp;
I understand your needs, but you need to do some transformations on the data.
(1)Click on “transform data” to enter the Power Query, right-click on the table, duplicate a table.

(2)&nbsp;Select columns KTATSTS, DTATSTS, and CTATSTS, and click the unpivot columns.

(3)&nbsp;Then click “Close and Apply” to return to PBI Desktop and create a measure.
Measure = COUNTROWS(FILTER(ALLSELECTED('Table (2)'),[TADOCD]=MAX('Table (2)'[TADOCD]) &amp;&amp; [Attribute]=MAX('Table (2)'[Attribute])))
(4)&nbsp;Place the field as shown below, it is different from your original x-axis, but if you place the TADOCD column on your x-axis then the numbers will be displayed in the middle bar and not spread out, this is due to the design.

&nbsp;
Best Regards,
Neeko Tang
If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.&nbsp;

anonymous · Answer

Hi&nbsp;AhmedT001&nbsp;,
&nbsp;
You do not need to copy three tables. All you need to do is convert the data to have a date column, a status column (which includes three statuses), and a value column. This is shown in the following figure:

&nbsp;
You can convert directly in the original table. The reason I mentioned duplicating the tables is because I didn't want to affect the structure of your original tables, so I duplicated one table.
&nbsp;
Best Regards,
Neeko Tang
If this post  helps, then please consider Accept it as the solution&nbsp; to help the other members find it more quickly.&nbsp;

ahmedt001 · Answer

Hello,&nbsp;Thank you for the very detailed response.&nbsp;One problem is the Transaction Statuses are all held on one file. So we have a Transaction Header file and that holds everything irrespective of status. In order for me to create KTATSTS, CTATSTS and DTATSTS, I've duplicated the Transaction file 3 times, then filtered to only show K, C or D respectively on each file and then the count is done on the visual. I tried lots of different ways to get the count to work and this seemed to be the only way I could get it to work, unless there is another way you know of?&nbsp;&nbsp;I'm not sure how I can get your method to work - which looks exactly what I need it to do, so thank you very very much for that, have my Kudos :).&nbsp;&nbsp;Hope this makes sense?&nbsp;

ahmedt001 · Answer

Hi,&nbsp;Thank you for the response. I still don't fully understand. Maybe I didn't explain myself properly, here are my tables:&nbsp;Transaction Header is the complete Transaction FileTransaction Detail is the complete Transaction Detail file which holds product identifiers, price, etc.&nbsp;TodaysCountOfKHeader is a subset of the main transaction header but only orders in K Status.&nbsp;TodaysCountOfKDetail is the above header table, merged with the Transaction Detail, so we have the product information of the items ordered in K Status. Each line in this table has the total cost of the item on order, in a column called LineValue, i.e. if 10 of a £5.00 item is ordered, the LineValue column will say £50.TodaysCountOfDHeader is a subset of the main transaction header but only orders in D Status.&nbsp;TodaysCountOfDDetail is the above header table, merged with the Transaction Detail, so we have the product information of the items ordered in D Status.&nbsp; Each line in this table has the total cost of the item on order, in a column called LineValue, i.e. if 10 of a £5.00 item is ordered, the LineValue column will say £50.TodaysCountOfCHeader is a subset of the main transaction header but only orders in C Status.&nbsp;TodaysCountOfCDetail is the above header table, merged with the Transaction Detail, so we have the product information of the items ordered in C Status. Each line in this table has the total cost of the item on order, in a column called LineValue, i.e. if 10 of a £5.00 item is ordered, the LineValue column will say £50.&nbsp;In my Visualisation:I have used the KTATSTS, DTATSTS and CTATSTS from the respective TodaysCountOfxHeader table as the Column Y-Axis, this is so that I can count the number of orders within each respective status. There are no counts or orders within any table, it's all done on the visual.I have used the KLineValue, DLineValue and CLineValue from the respective TodaysCountofxDetail table as the Line Y-Axis, this is so that I can get the values showing as points within each respective status.&nbsp;TADOCD is taken from the main Transcation Header Table.&nbsp;In your reply, are you saying to merge the respective separate Detail tables together so that they're all stored on one table and then follow your instructions or have I gone around the whole problem completely wrong in the first instance and that's why I'm confused or over complicated it?&nbsp;&nbsp;

Forum Discussion

Line and Clustered Column Chart - Points above Bars

4 Replies

Related content

Clustered column chart incomplete legend

Need a stacked clustered column chart

clustered column chart with 3 different data points that use 3 different date fields

Clustered Column Using Inscope

Multiple line and clustered columns chart