Forum Discussion

paveldavid17's avatar
paveldavid17
Frequent Visitor
2 years ago

Incorrect Matrix Total Calculations in Power BI – Expert Insight Required

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!

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    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's avatar
      paveldavid17
      Frequent Visitor
      2 years ago

      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's avatar
        MFelix
        Super User
        2 years ago

        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:

         




  • paveldavid17's avatar
    paveldavid17
    Frequent Visitor
    2 years ago

    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's avatar
      MFelix
      Super User
      2 years ago

      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.