Expenses as a percentage of branch revenue for a period

[MRoyW]

Hi, I have been stuck for hours trying to figure this out. I have two datasets - one with dates, branch names and expense accounts with values; the other with dates, branch names and revenue. These are of the form:

Expense table:

Date	Branch	PL item	Expense amount
2019/07/01	A	Transport	29332
2020/01/01	B	Meals	5647
2014/11/01	A	Cleaning	32562
2017/06/01	A	Training	13536

Sales table:

Date	Branch	Revenue
2018/05/01	C	67442
2016/02/01	D	33522
2020/03/01	A	1525
2017/05/01	B	5266

My objective is to show the expenses totals and also as a percentage of revenue (this is total for each branch and PL item for each filtered period) on Power BI.

I cannot figure out how to compute this accurately either on Alteryx or power BI.

Kindly someone help.

[MarqueeCrew]

If you take your expenses into a summarize tool, you can group buy branch and sum expenses.  Then you can join the sales table on branch.  

you might need to create a month field as left(Date field],7) that would be part of the group by in the summarize and joins. 

cheers,

 mark

[Qiu]

@MRoyW 
Basically, same as suggested by @MarqueeCrew , I made a sample workflow for your confirmation.
I have forged some data given the insufficient data volume provided.

[grazitti_sapna]

Hi @MRoyW , if you could provide some more data we can try on that else you can try this and summarize on the basis of Branch or PL as suggested.

In this workflow I have not used group by. It is total of all the revenue and expenses available. Please provide some more data for better understanding.

Thanks.

[atcodedog05]

Hi @MRoyW 

Please provide more info. Are you looking for an app where you can use user interface filters to calculate or is it just a workflow output.

Can you please provide more details on expected output.

We will be happy to help 🙂 

[MRoyW]

Hi @atcodedog05  @grazitti_sapna  @Qiu  @MarqueeCrew 

Thank you all for your assistance. I had done more or less the same thing that you all have suggested. I suppose my challenge is getting dynamic output that summarizes the expenses and maybe getting an average of the % of revenue for whichever period is selected.

For example, if someone wants to see branch level (periods extending column wise based on selection): 

	Year 1/ Qtr 1/ Month 1
Branch	Expense Amount	% of revenue

 
and if someone wants to see both the hierarchy with PL item:

	Year 1/ Qtr 1/ Month 1
	Expense Amount	% of revenue
A
Transport
Cleaning
B
Meals
Transport
Cleaning

In essence, the output is a table summary for any selection. I think an interface filter works best.

[grazitti_sapna · Accepted solution]

Hi @MRoyW , I have created a chained app try this if it satisfies your requirement.

1) 1st app for branch level output.

2) 2nd app for hierarchy level output.

After branch level app the hierarchy app will open itself. You can change the calculations as per your requirement.

If this is what you are looking for kindly mark this post as solution.

Thanks.