Calculated Column on Matrix Report

[Anonymous]

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 🙂 

 

[amitchandak]

@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]

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 No	Account Name	Company	Amount
1	Cash	Company A	10000
2	Cash	Company B	20000
3	Cash	Consolidated Company	30000
4	Fixed Assets	Company A	15000
5	Fixed Assets	Company B	25000
6	Fixed Assets	Consolidated Company	35000
7	Accounts Payable	Company A	100000
8	Accounts Payable	Company B	50000
9	Accounts Payable	Consolidated Company	150000
10	Accounts Receivable	Company A	50000
11	Accounts Receivable	Company B	20000
12	Accounts Receivable	Consolidated Company	70000

[Anonymous · Accepted solution]

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.