How to get Correct Total from the rows

[delmayhan]

Hi All,

I have a Matrix created in PowerBI with some fields ans use a filter Offsetweek. I don't get the correct Row level total and also the total is not correct. How can i get the correct row and total.   The measure i use is:
CALCULATE (
[Consensus Demand plan KG],
'Consensus Demand'[Snapshot] = MAX ( 'SnapshotMonthX'[Week_x_1] )
)

[123abc · Accepted solution]

To ensure you get the correct row-level total and overall total in your Power BI matrix, you need to make sure your DAX measure is correctly calculated. The measure you provided seems to filter the data based on the 'SnapshotMonthX'[Week_x_1] column, but it might need adjustments based on your specific requirements.

Here are some steps you can take to troubleshoot and potentially correct the issue:

1. Check Filter Context: Ensure that the filter context is correctly applied to your measure. In your case, you are using the CALCULATE function to apply a filter on the 'Consensus Demand'[Snapshot] column. Make sure this filter is working as expected and is filtering the data correctly.

2. Review Aggregation Logic: Check the logic inside your [Consensus Demand plan KG] measure to ensure it is calculating the correct values. The issue might not lie in the calculation of totals but in the underlying measure itself.

3. Inspect Relationships: Verify the relationships between your tables. In Power BI, correct relationships between tables are crucial for accurate calculations. Ensure that the relationships between the tables involved in your calculation are correctly defined.

4. Consider Row Context: Ensure that your measure is sensitive to row context. Measures in Power BI are evaluated within the context of the current row, so they should respond appropriately to changes in row context.

5. Test with Sample Data: Create a simple table or matrix visual with sample data and test your measure to see if it produces the expected results. This can help you isolate the issue and understand how your measure behaves in different scenarios.

6. Use DAX Studio for Debugging: Consider using DAX Studio, a tool for analyzing and debugging DAX queries. You can use it to inspect the queries generated by Power BI and identify any issues in your calculations.

Here's an example of how you can modify your measure to calculate the correct total:

CorrectTotal =
CALCULATE (
[Consensus Demand plan KG],
FILTER (
'Consensus Demand',
'Consensus Demand'[Snapshot] = MAX ( 'SnapshotMonthX'[Week_x_1] )
)
)

This measure filters the 'Consensus Demand' table based on the maximum value of the 'Week_x_1' column in the 'SnapshotMonthX' table and calculates the total.

Remember to adapt the measure according to your specific data model and requirements. If you're still facing issues, providing more details about your data model and the logic behind your measures would help in offering more targeted assistance.

[Anonymous]

Hi @delmayhan ,

Based on your description, I've modified the formula you provided: use the ALL function to remove the filter applied to the 'Consensus Demand' [Snapshot] column, while keeping the filter for 'SnapshotMonthX' [Week_x_1].

CorrectTotal = 
CALCULATE (
    [Consensus Demand plan KG],
    ALL('Consensus Demand'[Snapshot]),
    VALUES('SnapshotMonthX'[Week_x_1])
)

Another note: make sure there are no additional filters or sub-totals applied in visual, which may interfere with the calculations.

Best Regards,
Adamk Kong

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

[delmayhan]

Hi Thanks for this option. Only the values changes also because of the new calculation. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...