Hi, I have an issue and I don't have idea how solve it.. I need help.. please.. I am creating a balance report in PBI, in a File I have the menu with the options (rows) to display and each row has the group of balance sheet accounts to summarize. It is something like this: Report_NamecodeMenuLine#SubmenuRow Description SubmenuRow Description Account TypeAccount SubCat3Cat6Main ExpensesA0026A0026Other ExpensesA00261Maintenance Uniforms10101900REC*AllMain ExpensesA0026A0026Other ExpensesA00262Misc.10101360RECOTHMain ExpensesA0026A0026Other ExpensesA00265Misc.10101990REC*AllMain ExpensesA0026A0026Other ExpensesA00269tablecloths73071002RECMAR On the Other hand, I have another file with a balance of transactions to summarize by group of accounts It is something like this: ObjectSubDescriptionBU Cat03BU Cat06JANFEB10101900UniformsRECMAR 100.00 743.3410101900UniformsMOPMAR 95.00 - 10101900UniformsRECSEA 302.00 488.0010101900UniformsMOPMAR 35.00 65.0010101990MiscellaneousRECMAR 122.00 60.0010101990MiscellaneousMOPMAR 85.00 120.0010101990MiscellaneousRECSEA 100.00 101.4910101990MiscellaneousMOPMAR 505.00 - 10101990MiscellaneousRECSEA 12.00 365.0010101990MiscellaneousH1HSEA 120.00 11.0010101990MiscellaneousRECMAR 878.00 - 73071002Uniforms - QualifiedRECOTH - 250.0073071002Uniforms - QualifiedPHOHTL 200.00 100.0073071002Uniforms - QualifiedRECMAR 345.00 500.0073071002Uniforms - QualifiedFOOHTL 122.00 55.0073071002Uniforms - QualifiedRECSEA 250.00 300.00 Now, in the report the idea is display 1 row with the total, in this Case Main ExpensesExpenses typeJANFEB Other Expenses 2,691.00 2,888.83 Now, the problem I have is Summarize the amounts depending of the category codes (Cat3 and Cat 6), because sometimes I have specific Cat3 and Cat 6, by example the code A00269 have Cat3='REC' and Cat6='MAR', but in the other cases I have a specific Cat 3, but with all Category code 6 (By example the code A00261 has Cat3='REC' but with all category code 6 (Cat6). I can't create a relationship between both files, due there are a lot of records with distinct Category code 6 and combinations If any of you have an idea of how to handle this type of case, could you guide me on how to resolve it?

Helper V

4 years ago

Solved

Create a relationship using Specific or All values

Hi,

I have an issue and I don't have idea how solve it.. I need help.. please..

I am creating a balance report in PBI, in a File I have the menu with the options (rows) to display and each row has the group of balance sheet accounts to summarize.

It is something like this:

Report_Name	codeMenu	Line#	Submenu	Row Description	Submenu	Row Description	Account Type	Account Sub	Cat3	Cat6
Main Expenses	A002	6	A0026	Other Expenses	A00261	Maintenance Uniforms	1010	1900	REC	*All
Main Expenses	A002	6	A0026	Other Expenses	A00262	Misc.	1010	1360	REC	OTH
Main Expenses	A002	6	A0026	Other Expenses	A00265	Misc.	1010	1990	REC	*All
Main Expenses	A002	6	A0026	Other Expenses	A00269	tablecloths	7307	1002	REC	MAR

On the Other hand, I have another file with a balance of transactions to summarize by group of accounts

It is something like this:

Object	Sub	Description	BU Cat03	BU Cat06	JAN	FEB
1010	1900	Uniforms	REC	MAR	100.00	743.34
1010	1900	Uniforms	MOP	MAR	95.00	-
1010	1900	Uniforms	REC	SEA	302.00	488.00
1010	1900	Uniforms	MOP	MAR	35.00	65.00
1010	1990	Miscellaneous	REC	MAR	122.00	60.00
1010	1990	Miscellaneous	MOP	MAR	85.00	120.00
1010	1990	Miscellaneous	REC	SEA	100.00	101.49
1010	1990	Miscellaneous	MOP	MAR	505.00	-
1010	1990	Miscellaneous	REC	SEA	12.00	365.00
1010	1990	Miscellaneous	H1H	SEA	120.00	11.00
1010	1990	Miscellaneous	REC	MAR	878.00	-
7307	1002	Uniforms - Qualified	REC	OTH	-	250.00
7307	1002	Uniforms - Qualified	PHO	HTL	200.00	100.00
7307	1002	Uniforms - Qualified	REC	MAR	345.00	500.00
7307	1002	Uniforms - Qualified	FOO	HTL	122.00	55.00
7307	1002	Uniforms - Qualified	REC	SEA	250.00	300.00

Now, in the report the idea is display 1 row with the total, in this Case

Main Expenses	Expenses type	JAN	FEB
	Other Expenses	2,691.00	2,888.83

Now, the problem I have is Summarize the amounts depending of the category codes (Cat3 and Cat 6), because sometimes I have specific Cat3 and Cat 6, by example the code A00269 have Cat3='REC' and Cat6='MAR', but in the other cases I have a specific Cat 3, but with all Category code 6 (By example the code A00261 has Cat3='REC' but with all category code 6 (Cat6).

I can't create a relationship between both files, due there are a lot of records with distinct Category code 6 and combinations

If any of you have an idea of how to handle this type of case, could you guide me on how to resolve it?

Need Help

MFelix

4 years ago

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.

accounts_subs.pbix38 KB

gomezc73
4 years ago
That command CrossJoin works fine. I really appreciate your help.. thank you very much!!

Forum Discussion

Create a relationship using Specific or All values

10 Replies

Related content

Creating relationships

Relationship

Create relationship dialog box

Relationships

Relationship Issue