Dax - Creating a Measure

[Anonymous]

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.

TablePane

[amitchandak]

@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.

[Anonymous]

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

Financial Category Template

Desired Report Outlook

[v-zhenbw-msft · Accepted 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]

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 ? 

[v-zhenbw-msft]

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.