Hey @hb0135,
Thanks for sharing the data!
Based on your Excel dataset, you have:
- Element group name: Category identifier (A)
- Value: Numeric values for calculations
- Actual Month: Time dimension (Jan, Feb, etc.)
DAX Solution for % of Column Total in Line Graph:
Step 1: Create Base Measure (if not existing)
Total Value = SUM('YourTable'[value])
Step 2: Create % of Column Total Measure
% of Column Total =
DIVIDE(
[Total Value],
CALCULATE(
[Total Value],
ALLEXCEPT(
'YourTable',
'YourTable'[Actual Month]
)
)
) * 100
Alternative Formula:
Column Percentage =
VAR CurrentValue = [Total Value]
VAR MonthTotal =
CALCULATE(
[Total Value],
REMOVEFILTERS('YourTable'[Element group name]),
VALUES('YourTable'[Actual Month])
)
RETURN
DIVIDE(CurrentValue, MonthTotal, 0) * 100
Implementation Steps:
- Import your Excel data into Power BI
- Create the DAX measures above in your model
- Build Line Chart:
- Axis: Actual Month
- Legend: Element group name
- Values: % of Column Total measure
- Format the visual:
- Set Y-axis to percentage format
- Enable data labels if needed
Expected Result:
- Each month's line values will sum to 100%
- Shows proportion of each element within that specific month
- Matches the matrix % of Column Total behavior you showed earlier
Key Notes:
- Replace 'YourTable' with your actual table name
- Ensure month field is properly formatted as date/text
- The measure calculates percentage within each month context
- Values will represent each element's share of the monthly total
This solution will give you the column-wise percentage calculation in your line graph, similar to what you're seeing in the matrix visualization.
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer
