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 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 |
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!!