How do I multiply or divide values in report according to set criteria

[buinia]

Hi there, I am very new to Power BI and DAX in particular so I would really appreciate if someone could help me on the below issue with the report that I have been stuck for days.

I have Billings by Media Type and Revenue by Media Type set out as below (the required format of the report). I also need to calculate the Gross Margin (Revenue/Billings) by Media Type and present it with the same format below the current data table.

Could you please advise how I can create this new Gross Margin table?

Many thanks

Vivian

[achinm45 · Accepted solution]

If I have got you right, you want gross margin for every month in same year.

I will recommend you to change format of report.

1) Use your Billing/Revenue , Media Type columns for slicing and dicing.

2)  For every month if you need gross margin, make Gross Margin as one of measures.

3) Define measure like   M1 = Calculate ( SUM(Billing )/SUM(Revenue))

4) Now on power BI report sheet, take matrix and bring this M1 on it. So this will show you net Gross Margin for all years

5) Slice this with Time . Then for that respective years or months, this measure will show u Gross Margin for that month.

6) Do likewise for Access Type

[buinia]

Hi achinm45,

Thank you so much for helping me with the above question. I have followed your answer and it worked perfectly well.

I have another few questions which I would really appreciate if someone can help point me to the correct direction.

1. Staff costs - I have 2 staff tables (Staff Timesheet and Staff Hourly Rates) loaded by month and would like to be able to multiply these 2 tables to get the monthly staff costs by Staff Department (SBU). Please note that both time and hourly rate can change so I can't use Measure = SUM(Staff Timesheet)*AVERAGE(Staff Hourly Rate) since it will just take the average staff hourly rate and not the accurate rate for the month.

2. Net Profit - Net profit is equal to Revenue - Staff Costs which should be straight forward. However since there are some Revenue Department (SBU) does not appear in Staff Department (SBU) as some revenue are generated as an off product of another type of revenue and does not require direct staff resources. Eventhough I have created an SBU Master table which included all SBUs, I still find that when I slice the report by SBU (from the SBU Master), the report still calculate some Staff Costs value for those SBUs that does not exist in Staff Department instead of leaving it as 0 which mean Net Profit = Revenue.

Thank you in advance for your help!! Vivian

[achinm45]

Hi @buinia,

Thank You for trying the approach and your appreciation !

For Staff Costs and Net Profit, I think if you can provide more data or sample data in sheet, it may help.

As far as I can say , this problem may need data modeling to combine staff costs, number of hours and Revenue from SBUs.

If you can probably look in that side to model the data, it will solve your problem.

Regards,

Achin