
1. Re: Row Level Ratio from the same dimension in
Deepak Rai Jun 29, 2018 9:28 AM (in response to Lolakshi Gour)Hi Lolakshi,
Please attach some data
Thanks
Deepak

2. Re: Row Level Ratio from the same dimension
Lolakshi Gour Jun 29, 2018 9:31 AM (in response to Deepak Rai)Hi Deepak,
I have included some sample data table in the post. Please let me know if it helps. Thanks

3. Re: Row Level Ratio from the same dimension
Deepak Rai Jun 29, 2018 9:33 AM (in response to Lolakshi Gour)That is scattered all around. We need some data in proper columns to help you only.

4. Re: Row Level Ratio from the same dimension
Lolakshi Gour Jun 29, 2018 9:34 AM (in response to Deepak Rai)I am Sorry. I just edited my post again. Is it okay now?

5. Re: Row Level Ratio from the same dimension
Deepak Rai Jun 29, 2018 9:48 AM (in response to Lolakshi Gour)Here It Is.
Pl Check and If it Helps. As I understand, you need to Divide all of them by 650000 only.
Thanks
Deepak
If it Helps, Pl mark it Helpful and CORRECT to Close Thread

Ratio.twbx 27.7 KB


6. Re: Row Level Ratio from the same dimension
Lolakshi Gour Jun 29, 2018 11:40 AM (in response to Deepak Rai)Hi Deepak, Thanks for your response. On looking at your workbook, I realized that you have under DEnominator measure as {MIN([Fy 2018])}. There is an issue with this logic as there may be some values for node_parent_names whose value might be less than the revenue amount. Is there any work around for this?

7. Re: Row Level Ratio from the same dimension
Deepak Rai Jun 29, 2018 11:44 AM (in response to Lolakshi Gour)I kept that Denominator because you wanted to divide by 650000 and it was Minimum, So what is the Logic for denominator for MERCHDSE and For REVENUE?

8. Re: Row Level Ratio from the same dimension
Lolakshi Gour Jun 29, 2018 11:59 AM (in response to Deepak Rai)So the logic is to get the ratio i.e.
Numerator: individual gl code amounts for MERCHANDISE and payroll (REVENUE not included)
Denominator : total revenue amount i.e. 650000
for 5001: 106215/650000
for 5002: 60490.79/650000
and so on....
I am sorry. I know its my fault. I am not able to explain it properly.

9. Re: Row Level Ratio from the same dimension
Deepak Rai Jun 29, 2018 12:10 PM (in response to Lolakshi Gour)In your Data, You don't have payroll, So just use this calculation for Ratio. It will give you for MERCHDSE and Payroll ONly
{FIXED [Region Id],[Node Parent Name],[GL Code]:SUM(IF [Node Parent Name]<>"REVENUE" THEN [Amounts] END)}/{MIN([Fy 2018])}
If it Helps, Pl mark it Helpful and CORRECT to close Thread
Thanks
Deepak

Ratio.twbx 27.8 KB


10. Re: Row Level Ratio from the same dimension
Lolakshi Gour Jun 29, 2018 12:49 PM (in response to Deepak Rai)The logic {MIN([FY 2018]} may not work as you are taking the minimum value for FY 2018. there may be some values below the revenue amounts.

11. Re: Row Level Ratio from the same dimension
Deepak Rai Jun 29, 2018 12:59 PM (in response to Lolakshi Gour)That's What I am asking, What is the Logic For Denominator. Why you should Divide by 650000? So What do you want in Denominator?

12. Re: Row Level Ratio from the same dimension
Lolakshi Gour Jun 29, 2018 4:26 PM (in response to Deepak Rai)Numerator: Non Revenue Amount = IF([NODE_PARENT_NAME (group)] <> "REVENUE") THEN ([REPORTED_AMOUNT]) END ( All Node_parent_names except REVENUE)
Denominator: Revenue Amount=IF([NODE_PARENT_NAME (group)] = "REVENUE") THEN ([REPORTED_AMOUNT]*(1)) END ( Which is just the REVENUE)
I have attached the data in excel format. Please let me know if it helps

Test.xlsx 43.3 KB


13. Re: Row Level Ratio from the same dimension
Deepak Rai Jun 30, 2018 3:38 PM (in response to Lolakshi Gour)I am Totally Confused ...I don't Know You Want To Divide what by What.. But As per Your above Logic...Here is Screenshot..Please provide clear Logic with example from above.Your attached data is not in proper format to bring it in Tableau.
Thanks
Deepak

Ratio (3)_v10.2.twbx 27.6 KB


14. Re: Row Level Ratio from the same dimension
Lolakshi Gour Jul 1, 2018 2:14 AM (in response to Deepak Rai)Hi Deepak,
Let me provide with an example. From the excel sheet, if you add up all the amounts for GL code under Revenue(From NODE_PARENT_NAME dimension). For 2017, its 8,630,018.49$ which is total revenue amount..
So, the total revenue amount is in the denominator. It is calculated based on the formula IF([NODE_PARENT_NAME (group)] = "REVENUE") THEN ([REPORTED_AMOUNT]*(1)) END
Similarly, there are all gl codes for Expense(from Node_parent_name dimension which is considered as NON REVENUE). for example gl code 5106 amount is $33941.83,6000 amount is $ 201,079.26 and so on.
The numerator logic for gl code 5106 amount (33941.83$) is IF([NODE_PARENT_NAME (group)] <> "REVENUE") THEN ([REPORTED_AMOUNT]) END
So, I want a ratio column where for 5106 glcode is 33941.83$ divided by total revenue amount 8,630,018.49 i.e. 33941.83/8630018.49
Similarly, for 6000 glcode the ratio is 201,0709.26/8630018.49
similarly it applies to all the gl codes for all the node_parent_names.
I have attached an image on how am i representing the data.
Please let me know if I am clear know ....

Image.PNG 52.4 KB
