filter calcultated measure from table without relationship

[Evandam]

Hi,

is was wondering if there is a solution for the following problem:

i have the following tables

dim team 

Team

A

B

Financial data (see below)

Budget data (see below)

==================

i have a table with financial data which i not already aggregated, and i have another table with budgets per GL account (so is already aggregated). I would like to "summarize" the financial data in a new created table using calculate and sum, but stille be able to use a slicer to filter the visual based on the selected team.

financial data

Team	GL account	GL description	Aggregation	amount
A	8000	Revenue domestic	1	500
A	8000	Revenue domestic	1	250
A	8100	Revenue forgein	2	200
A	8100	Revenue forgein	2	50
B	8000	Revenue domestic	1	125
B	8000	Revenue domestic	1	100
B	8100	Revenue forgein	2	300
B	8100	Revenue forgein	2	400

via measure i calculate the sum for each aggregation level

Revenue_domestic = CALCULATE(SUM('Financial data'[amount]);'Financial data'[aggretation level]=1)

Revenue foreign = CALCULATE(SUM('Financial data'[amount]);'Financial data'[aggretation level]=2)

Budget data

Team	GL account	Aggregation	budget
A	8000	1	700
A	8100	2	275
B	8000	1	200
B	8100	2	700

via measure i calculate the sum for each aggregation level:

Budget domestic = CALCULATE(SUM('Budget data'[Budget]); 'Budget data'[aggregation]=1)

Budget foreign = CALCULATE(SUM('Budget data'[Budget]);'Budget data'[aggregation]=2)

no i make a new table via enter data. i define the colums: "aggregation level" and description.

Aggregation level	description
1	domesic
2	foreign

i then create a new column for revenue and budget and "assign" the measures to the proper row an IF statement:

Revenue = IF([Aggregation level]=1;[Revenue_domestic];[Revenue foreign])

Budget = IF('calculated table'[Aggregation level]=1;[Budget domestic];[Budget foreign])

this table i will use in a matrix visual resulting in this overview:

No i want to add a slicer (from Team table) and for instance slice on team A. the data should than look like this

Aggregation	revenue	budget
1	750	700
2	250	275

the hard thing in my opinion is that when making a relationship to the financial table and budget table form the calculated table, the financial table and budget table are on the many side, whilst the calculated table is on the one side. 

creating a bidirectional relationship will only "work" for 1 table; fincial OR budget, so this is not a solution.

Is it possible to use a dax function to get te desired result?

[Jayee]

Hi @Evandam

If my understanding is correct you are expecting this table and filter by team.

Aggregation	revenue	budget
1	750	700
2	250	275

In table visual take aggregation from calculated table, revenue from financial data and budget from budget table and take team from dim team table in slicer it will work perfectly.

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!

[mangaus1111 · Accepted solution]

Hi @Evandam ,

you don´t need to create a calculated table, bidirectional relationships or something complicated like this.

See my pbi file and let me know if this is your expected result.

https://1drv.ms/u/s!Aj45jbu0mDVJi1-_MV3go4RnUpSZ?e=mawEr4

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

[speedramps]

We want to help you but your description is too vaugue. Please write it again clearly.

Please DON'T copy & paste your DAX and relationships that does not work and expect us to fathom what you want to do. That is a bit crazy. 😀

Please just give a simple non technical functional decscription of what you want, then let us suggest the DAX. Thank you.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you. Also provide the example desired output, with a clear description of the process flow.

Please click here to download an example PBIX 

Remember not to share private data ... we don't want you to get into trouble. 😧

Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.

You will get a quick response if you put time, care and effort into writing clear problem descriptions.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming