Adding a formula in power pivot

[ahsanaq]

Hello Experts,
I would like your help regarding a problem I am facing when using power pivot. I have three excel sheets containing different data. Sheet 1 contains customer wise revenue, Sheet 2 contains customer wise month end receivables and sheet 3 contains full list of customer along with their details. I was able to create a relationship between the three and was able to create a power pivot showing customer wise (and month-wise) revenue and receivable. now I need to apply a formula. I want to powerBI to calculate the three months average receivables divide it by total revenue and multiply it by 90

I was able to add a "measure" where by receivable balance was divided by 3, and I added that in the pivot. but I am stumped as to what to do next.

PS: I am new to PowerPivot, hence excuse my lack of knowledge.

Thanks in advance

 

[Matt Allington]

You need time intelligence. Read my article here DAX Time Intelligence Explained - Excelerator BI

 

[ahsanaq]

You need time intelligence. Read my article here DAX Time Intelligence Explained - Excelerator BI

Hi Matt,

Thanks for your help. from what I can gather, the time intelligence will help me calculate the three month average receivables without going back every time and selecting which three months to use. however my real problem is adding the formula. how do I divide the two fields (total sales(revenue) with average receivables (both are appearing in the pivot).