Adjusting DAX Formula for Percentage Calculation

Problem Overview

The issue arises from a DAX formula that calculates a new column نسبة_الوزن, producing the same value (100%) for all rows in a dataset. The goal is to adjust this calculation to ensure that the sum of the new column results in 100%, with each individual value reflecting its proportion relative to the total.

Correct Approach

The primary task involves modifying the DAX formula to compute the percentage of each row with respect to the sum of the entire column. This can be achieved by ensuring that the denominator sums up correctly inside the DIVIDE function on a row-by-row basis.

Revised DAX Code

نسبة_الوزن =
DIVIDE(
    'تجميع_القطاعات_معدل'[قيمة العقد المعدلة مع الضريبة],
    CALCULATE(SUM('تجميع_القطاعات_معدل'[قيمة العقد المعدلة مع الضريبة]),
              ALL('تجميع_القطاعات_معدل'))
)

Explanation

• DIVIDE Function: Safely performs division, returning BLANK() in case of division by zero.
• Numerator: The value of the current row - 'تجميع_القطاعات_معدل'[قيمة العقد المعدلة مع الضريبة].
• Denominator:
  • CALCULATE: It recalculates the sum of the specified column.
  • ALL('تجميع_القطاعات_معدل'): This part removes any filters that may distort the overall total sum, allowing the sum of the entire dataset to be considered for the percentage calculation.

Code Usage Example

1. Dataset: Assume we have a table named تجميع_القطاعات_معدل with a column قيمة العقد المعدلة مع الضريبة containing contract values.

2. Implementation: Create a new column in your data model using the above DAX formula.

3. Output: Each row in the new column نسبة_الوزن will now correctly display the weight (percentage) of that row’s قيمة العقد المعدلة مع الضريبة compared to the total for the entire column, summing to 100% across all rows.

Conclusion

This modified DAX formula effectively calculates the percentage weights for each row and aggregates them to ensure they sum up to 100%. By using the ALL function within the CALCULATE, we prevent any potential filtering issues, providing an accurate representation of the data.

For deeper understanding and more complex scenarios, consider exploring courses on the Enterprise DNA Platform that delve into advanced DAX techniques and best practices.