I attached my database and a Tableau file in order to help you to illustrate the possible solution you may find to my problem. Thank you in advance for taking the time on reading this and trying to help me.
Sorry if you find the title is very vague, but I could not find a better one to explain my situation. I will try to illustrate better my problem with the following comments:
I have a data composed by 4 different Financial Items: 1-Accounts Receivable (AR), 2-Past dues (PD), 3-Sales and 4-Days Sales Outstanding (DSO). The data is divided by country, sub-region and region. I have data for this year and the previous one. My idea is to compare the variations of these Financial Items
I took the Sales, Past dues and AR from my datasource and then I made the calculation of DSO in the excel file at country level following this formula: DSO= AR/Sales.
My problem appears when I select a "high level" view. For instance, if I take the Regional view, the DSO is calculated adding all the individual DSO of each country member of that region rather than taking the total amount of AR and Sales and applying the formula. The results are big values that do not make sense with the reality (see the picture below)
As you can see, is not possible to have a DSO of 21k in EMEA.
After uselessly trying to solve this issue, I decided to Create measures of each Financial Item in order to be able to solve this calculation issue. Well, I was able to do so, however i could not add the calculations vs PY$ and vs PY% to each of them because is not possible to apply measures to other measures. Hence, I got the real DSO but I have now the problem of not having the variances Actual vs Pior Year (See the picture below):
So basically my problem is that I cannot get both the correct DSO and the variations vs PY$ and vs PY% in the same table. I don't know if manipulating my database I could do it but I could not think any way to do it. Please, do not hesitate on asking for more info or if you have any doubt.
Thank you very much