Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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:
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:
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?
Solved! Go to 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.
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!!
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.
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
91 | |
88 | |
35 | |
35 |
User | Count |
---|---|
153 | |
99 | |
82 | |
63 | |
54 |