Forum Discussion
Create a relationship using Specific or All values
Hi gomezc73 ,
Try the following formula:
Total Values = VAR temp_table = FILTER ( CROSSJOIN ( 'Balance sheet accounts', 'Balance Transactions' ), 'Balance sheet accounts'[Account Sub] = 'Balance Transactions'[Sub] && 'Balance sheet accounts'[Account Type] = 'Balance Transactions'[Object] && 'Balance sheet accounts'[Cat3] = 'Balance Transactions'[BU Cat03] && ( 'Balance sheet accounts'[Cat6] = 'Balance Transactions'[BU Cat06] || 'Balance sheet accounts'[Cat6] = "*All" ) ) RETURN SUMX ( temp_table, 'Balance Transactions'[Value] )This is giving the correct result for the small sample you present however not sure if it will return correctly on your overall values since the details can make all the difference:
Also be aware that I have unpivot the transactions in order not to have a calculation for each column.
Check PBIX file attach.
That command CrossJoin works fine. I really appreciate your help.. thank you very much!!
Hi, In my Report i need display only 1 line with the summary by Account OBJ, Account Sub, Cat3 and Cat 6..
I didn't understand your solution, do yo mean, create a table for Categories 3(Cat3), other table for Categories 6 (Cat 6)?.. ?.. how I can relate each tabla with my current tables?
Hi gomezc73 ,
When I refer a relationship is a model similar to this one:
Has you can see there is a relationship between both tables done by the dimension tables.
My question now refers to CAT6 since you have on the first table a value of ALL will you have a line for eaxch one, so if you do a model similar to the one above it will work properly or will you have the value ALL in the CAT 6 and you want to pick up all the CAT on the transactions even if they are not explicit in the Group balance?
Hi Miguel,
Using your image 'Group Balance" When the field Cat6='ALL', I don't have in the file of details records with CAT6='ALL', because that value is not a valid CAT6 (It is only a reference value to identify that in the second table can exist multiples CAT6 that must be summarize)
By example, in the file of details can exist records with CAT6 with these values ("MAR","COE","REP","RWE","PEE", etc) (Exist around 30 valid CAT6).
Then, The idea is:
A) If I found a record in the First table with CAT6=ALL, then I need summarize the records in the Second table with CAT6 IN ("MAR","COE","REP","RWE","PEE", etc).
B) For any other value in the first table Distint to ALL, i can look the exact value in the second file, By Example: If I found a record in the First table with CAT6="MAR", then I need summarize only records in the Second table with CAT6="MAR".
I hope this help to understand my problem,
Hi gomezc73 ,
I think now I understand your need however I'm confused about the final outcome you need.
Has you see below if I pickup the January I get 2109 for the REC on CAT3 ifI compare this with the value you have (2.691) I'm missing 532. That I believe is the value of CAT6.
Can you please tell how you calculate dthe value in the example you gave
Hi, Sorry I have an error in my calculations.
I did a new table with both Files, the total for JAN must be 1859.
Table1 Report_Name Table1 Row Description Table1 Row Description Table1 Account Type Table1 Account Sub Table1 Cat3 Table1 Cat6 Table2 Object Table2 Sub Table2 Cat03 Table2 Cat06 JAN FEB Main Expenses Other Expenses Maintenance Uniforms 1010 1900 REC *All 1010 1900 REC MAR 100.00 743.00 1010 1900 REC SEA 302.00 488.00 Main Expenses Other Expenses Misc. 1010 1360 REC OTH Main Expenses Other Expenses Misc. 1010 1990 REC *All 1010 1990 REC MAR 122.00 60.00 1010 1990 REC SEA 100.00 101.00 1010 1990 REC SEA 12.00 365.00 1010 1990 REC MAR 878.00 - Main Expenses Other Expenses tablecloths 7307 1002 REC MAR 7307 1002 REC MAR 345.00 500.00 Main Expenses 1,859.00 2,257.00