Incorrect Matrix Total Calculations in Power BI – Expert Insight Required

[paveldavid17]

Hello everyone,

I'm currently facing a challenging issue with Matrix Total calculations in Power BI that I haven't been able to resolve despite extensive research, including videos and several forums like StackOverflow and the Fabric forum.

Problem Description: I am working with a matrix where I need to perform the following operations:

1. From the grand total of Column A, I subtract the values from Column B. The results here are accurate.
2. However, the problem arises with Column C. I need to calculate the grand total for this column, but the results are consistently incorrect. The expected grand total should be 3.7101783176%, but all I can achieve through the matrix in the PBIX file is 3.62000561681% (as illustrated in the first picture I've attached).

Additional Context:

• The matrix features a drilldown hierarchy which I need to respect. This means that the total for level 1 of the hierarchy should be the sum of level 2, and the grand total should reflect the sum of all levels.
• I also use Parametric Fields for switching between different hierarchies, but I can get rid off this feature, if it means solving this problem.

Question: How can I adjust my calculations to respect the correct context within the matrix, ensuring that the totals at each hierarchy level and the overall grand total are accurate?

Any insights or suggestions would be greatly appreciated. Thank you for your help!

[paveldavid17]

Unfortunately, this doesn't give me the right result. I get 6.41% instead of the requested 3.71%.

btw: of course I modified the code for my needs

Do you have any other idea please?
Thank you 🙏

[MFelix]

Hi @paveldavid17 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[MFelix]

Hi @paveldavid17 ,

Since you are doing the difference between the two column and using a measure the grand total is also part of that difference, best options is to do a SUMX however this needs to be done taking into account the others columns on your visualization.

What are the names of the other columsn you use for the hierarchy?

[paveldavid17]

Hello @MFelix ,

As I've noted earlier, while I employ parametric fields in my operations, for the sake of simplicity, let's consider that I'm utilizing a single hierarchy—specifically Customer hierarchy. This hierarchy is composed of the following columns within the 'MD_Customer' table:

• 'MD_Customer'[Consolidated Key Customer]
• 'MD_Customer'[Key Customer]
• 'MD_Customer'[SoldtoName]

[MFelix]

Hi @paveldavid17 ,

In this case since the values are all from the same table (MD_Customer) you can try the following code:

C measure =
SUMX (
    SELECTCOLUMNS (
        Customer,
        Customer[Consolidate Key Customer],
        Customer[CustomerKey],
        Customer[SoldtoName]
    ),
    [CALCULATION YOU USE FOR C MEASURE]
)

Altough the example below is not fully matching yours the logic is the same: