Clustered Column Chart - % Row Total

[Turquesa]

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!

[Ashish_Mathur · Accepted solution]

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.

[Anonymous · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[Daniel29195]

@Turquesa

im not sure what your requirement . 

visual representation of your requirement would be helpful . 

best regards.