Forum Discussion
Clustered Column Chart - % Row Total
Hi Community!
I need help with a clutered column chart! I would like to show in the chart the invoices received by month (month X-axis) and I have added some invoice buckets in the Y-axis. At the moment I have achieved to display the info showing the number invoices but I would like to show the values as a % of row total (i.e. Jan-23, buket 1: 30%, bucket 2:50%, bucket 3:20%) but I can only find the option of grand total.
I would like to also do this in a matrix table. Please note that some invoices don't have an invoice bucket (cells are blank). I would appreciate if you could help me out with this! I'm fairly new to Power BI.
Many thanks in advance!
Hi,
Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name by the Month number. Create a relationship (Many to One and Single) from the Date column in your Data Table to the Date column in the Calendar Table. To your visual/slicer/filter, drad year and Month name from the Calendar Table. Assuming you already have a bucket column in your base data table, write these measures
Total = sum(Data[Invoice value])
Total across all buckets = calculate([Total],all(Data[buckets]))
Proportion = divide([Total],[Total across all buckets])
For the proportion measure as a %. Drag the proportion measure to your visual.
Hi Turquesa ,
I can provide you two solutions:
The first solution:
Put both of the "Month" and "Bucket" into X-axis:Modify the display of Value to %:
Click here:
When you want to see the percentage of each Bucket each Month, please follow the chart below:
For example, I want to see the Month 1, click 1 and click 2:The final output is below:
The second solution:
Choose this visual:Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
im not sure what your requirement .
visual representation of your requirement would be helpful .
best regards.
Hi,
Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name by the Month number. Create a relationship (Many to One and Single) from the Date column in your Data Table to the Date column in the Calendar Table. To your visual/slicer/filter, drad year and Month name from the Calendar Table. Assuming you already have a bucket column in your base data table, write these measures
Total = sum(Data[Invoice value])
Total across all buckets = calculate([Total],all(Data[buckets]))
Proportion = divide([Total],[Total across all buckets])
For the proportion measure as a %. Drag the proportion measure to your visual.
Hi Turquesa ,
I can provide you two solutions:
The first solution:
Put both of the "Month" and "Bucket" into X-axis:Modify the display of Value to %:
Click here:
When you want to see the percentage of each Bucket each Month, please follow the chart below:
For example, I want to see the Month 1, click 1 and click 2:The final output is below:
The second solution:
Choose this visual:Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
