Drill up from Month to Quarter, the percentages are showing as a cumulative instead of average val

[Anonymous]

Hi,

When I drill up from Month to Quarter, the percentages are showing as a cumulative instead of average value. Kindly help me to fix this

After drill up

Dateset

Thanks

[Anonymous · Accepted solution]

Hi, Thank you so much for the suggestion. I was able to fix the issue. Thanks

[123abc]

Plz can you share the PBIX file ?

OR plz chek the below suggestion

1. Check your Data Model: Ensure that your data model is properly structured, and you have a clear relationship between the Month and Quarter tables.

2. Use AVERAGEX Function: If you're using a measure to calculate the percentage, modify it to use the AVERAGEX function. For example, if you have a measure called "Percentage," it might look like this initially:

Percentage = SUM('YourTable'[Value]) / SUM('YourTable'[Total])

Change it to:

Average_Percentage = AVERAGEX(VALUES('YourTable'[Month]), [Percentage])

1. This modification ensures that the percentage is averaged over the months.

2. Consider Time Intelligence Functions: If you're dealing with time-based data, Power BI has built-in Time Intelligence functions. You might use functions like TOTALYTD, TOTALQTD, or TOTALMTD instead of simple aggregation. For example:

Average_Percentage = DIVIDE([Percentage], CALCULATE([Percentage], ALL('YourTable'[Month])))

1. This example uses the DIVIDE function to calculate the average percentage.

2. Adjust Visualization Settings: Sometimes, the issue might be in the visualization settings. For certain visualizations, you can set the aggregation method. For instance, if you're using a table or matrix, you can adjust the value field settings.

3. Review Aggregation in Relationships: Ensure that the relationships between your tables are set up correctly. Sometimes, the default behavior of aggregation is influenced by the relationships in the data model.

4. Use DAX to Control Aggregation: You can explicitly control aggregation using DAX functions like SUMX or AVERAGEX in your measures.

Remember to replace 'YourTable' and 'YourColumn' with your actual table and column names. The exact solution depends on your specific data model and calculations.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.