Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am new to power BI and would like to know if anyone will be able to assist with the following query.
I have created a table which contains my financial items and my raw data actuals. However i was wondering whether i can create an actuals measure instead which will allocate all my data to the specific rows.
Solved! Go to Solution.
Hi @Anonymous ,
We can create a calculate column and use the filter on this page to meet your requirement.
1. Create an actual column in Financial Category table.
Column =
var _actual =
CALCULATE(
SUM('Transaction Table'[Actuals ]),
FILTER('Transaction Table','Transaction Table'[Financial Statement Breakdown]='Financial Category'[financial statement list]))
return
IF(
ISBLANK(_actual),0,_actual)
2. Then we can create a table visual and configure like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.
See below Sample data. I have two tables in this model which i have created, however i have not created a relationship between the two tables!
I have created a table in which then i have brought in the category and financial statement list from the Financial Category template. I would then like to create a measure which would then e.g. allocate the actuals results from the transaction table to the every specific row within the report linked to the Financial statement list .
See below transaction table and Financial Category Template. In addition to report output
Transaction Table
Financial Statement Breakdown | Profit Centre | Month | Actuals |
Cash Sales | GB695 | 31/07/2020 | 24.81942 |
Total Revenue | GB695 | 31/07/2020 | 26.08325 |
Raw Mats | GB695 | 31/07/2020 | -15.56407 |
Pesonnel | GB695 | 31/07/2020 | -2.702 |
Other Direct Costs | GB695 | 31/07/2020 | -5.6985 |
Onerous Contract Provision | GB695 | 31/07/2020 | 0 |
Subcontract costs | GB695 | 31/07/2020 | 0 |
Depreciation | GB695 | 31/07/2020 | 0 |
OR Discount | GB695 | 31/07/2020 | 1.64542 |
Settlement Discount | GB695 | 31/07/2020 | 11 |
Segment Income | GB695 | 31/07/2020 | 18 |
SG&A | GB695 | 31/07/2020 | 21 |
Total Bad Debt | GB695 | 31/07/2020 | 2 |
UOP | GB695 | 31/07/2020 | 10 |
Field Contribution | GB695 | 31/07/2020 | 2 |
Cash Sales | IE016 | 31/07/2020 | 12.82202 |
Total Revenue | IE016 | 31/07/2020 | 21.82374 |
Raw Mats | IE016 | 31/07/2020 | -9.161192 |
Pesonnel | IE016 | 31/07/2020 | -11.05577 |
Other Direct Costs | IE016 | 31/07/2020 | -0.317719 |
Onerous Contract Provision | IE016 | 31/07/2020 | 0 |
Subcontract costs | IE016 | 31/07/2020 | 0 |
Depreciation | IE016 | 31/07/2020 | 0 |
OR Discount | IE016 | 31/07/2020 | 0.026881 |
Settlement Discount | IE016 | 31/07/2020 | 0.026881 |
Segment Income | IE016 | 31/07/2020 | 9 |
SG&A | IE016 | 31/07/2020 | 8 |
Total Bad Debt | IE016 | 31/07/2020 | 4 |
Field Contribution | IE016 | 31/07/2020 | 12 |
UOP | IE016 | 31/07/2020 | 20 |
Hi @Anonymous ,
We can create a calculate column and use the filter on this page to meet your requirement.
1. Create an actual column in Financial Category table.
Column =
var _actual =
CALCULATE(
SUM('Transaction Table'[Actuals ]),
FILTER('Transaction Table','Transaction Table'[Financial Statement Breakdown]='Financial Category'[financial statement list]))
return
IF(
ISBLANK(_actual),0,_actual)
2. Then we can create a table visual and configure like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Many thanks for this 🙂 @v-zhenbw-msft
Can you also please advise if its possible to freeze panes on a specific column within a Table in Power BI ?
Hi @Anonymous ,
I'm afarid it is still impossible currently. This is a similar idea you can vote to impove Power BI.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |