Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated Column on Matrix Report

Hi all

 

My data source has values for subsidiary companies, and an aggregated (consolidation) company. 

 

I am trying to set up a matrix report, with a final "check sum" column, that will highlight if anything does not balance between the subsidiary and the consolidated company. I can obviously get the values, rows and "normal" company columns, but not the check sum column.

 

I am not an expert with DAX in any way. Can anyone give me a pointer on how to get started with this? 

 

thanks 🙂 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I suggest you to try this measure.

    Measure = 
VAR _Consolidated_Company = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Company] = "Consolidated Company"))
VAR _CompanyA_CompanyB = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Company] <> "Consolidated Company"))
RETURN
IF(HASONEVALUE('Table'[Company]),CALCULATE(SUM('Table'[Amount])),_Consolidated_Company - _CompanyA_CompanyB)

    Then change the Column Subtotal name from "Total" to "Check". Result is as below.

    Best Regards,
    Rico Zhou

     

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

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
    Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi, I can give sample data, below

       

      You can hopefully see I am just trying to pivot this data, with Company as Column, Account Name as Row, sum of amount as value, but introduce a "check sum" column, to check the values of the Consolidation company against the (Company A + Company B)

       

       

      Row NoAccount NameCompanyAmount
      1CashCompany A10000
      2CashCompany B20000
      3CashConsolidated Company30000
      4Fixed AssetsCompany A15000
      5Fixed AssetsCompany B25000
      6Fixed AssetsConsolidated Company35000
      7Accounts PayableCompany A100000
      8Accounts PayableCompany B50000
      9Accounts PayableConsolidated Company150000
      10Accounts ReceivableCompany A50000
      11Accounts ReceivableCompany B20000
      12Accounts ReceivableConsolidated Company70000

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        I suggest you to try this measure.

        Measure = 
VAR _Consolidated_Company = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Company] = "Consolidated Company"))
VAR _CompanyA_CompanyB = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Company] <> "Consolidated Company"))
RETURN
IF(HASONEVALUE('Table'[Company]),CALCULATE(SUM('Table'[Amount])),_Consolidated_Company - _CompanyA_CompanyB)

        Then change the Column Subtotal name from "Total" to "Check". Result is as below.

        Best Regards,
        Rico Zhou

         

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