Create measures in Matrix visual without loosing the context

[amurt012]

Hi everybody,
I am working on developing a Power report in Microsoft Fabric. I am building a P&L statement in Power BI using Matrix visual. My Matrix visual is built using:  Rows-> Metric from DimMetric; Columns-> MonthYear from DimDate; Values-> Actual from FactSales.
I am trying to compute the metrics - Operating Profit, OPM%, Profit Before tax in the Matrix visual by creating measures  (as in Fabric I am unable to create New column).  These metrics have wrong data in the source hence need to be computed in the visual.

I am trying to display the Actual and ActualYTD (YTD from FY start ; FY - 1st April - 31st March) for the months - Nov-23, Dec-23,Jan-24 and so on. 

I am able to display the Actauls correctly which are coming from database but the issue is the measures I created for Actual and ActualYTD for the Operating Profit, OPM%, Profit Before tax are not displaying the correct values. I am trying to maintain the context while computing the metric, for example OPM% = Operating Profit / Net Sales. I want to SUM the Operating Profit and Net Sales before performing the division. I am unable to find a way how can I acheive this. 

I am attaching my Data model and the Power BI report sample layout. 

Looking forward for some help here. Thanks in Advance. 

[Anonymous]

Hi @amurt012 ，

I noticed that in the 'FactActuals' table the data is broken down by [year month], your 'DimDate' and 'FactActuals' are in a many-to-many relationship?
It is recommended that you create a new calculated column in 'FactActuals':

DateKey = 'FactActuals'[YearMonth]*100+1

And the relationship:

Take [Sales] for example:

Sales YTD = CALCULATE([Sales],DATESYTD('DimDate'[Date],"3-31"))

The pbix file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group