1 2 Previous Next 19 Replies Latest reply on Jul 2, 2018 9:19 PM by Mahfooj Khan

# Row Level Ratio from the same dimension

Hello All,

I am running into an issue and I have really hard time solving the issue. Let me explain my scenario.  I have a table as below. Under a region, there are multiple locations(for example 012). And each location has multiple node_parent_name example REVENUE, Merchandise, Payrol with their totals for 2018.

 region id location name/ number NODE_PARENT_NAME FY 2018 2 12 alexandria, va REVENUE 650000 MERCHDSE 17100000 PROD_PAYROLL 9950000

Under each node_parent_name, I have gl codes with their respective amounts.

 region id location name/ number NODE_PARENT_NAME GL Code Amounts 2 12 alexandria, va REVENUE 4001 -359144 4002 -146259 4003 -2151.06 4004 -213906 4005 -17355.4 and so on with all codes MERCHDSE 5000 691249.3 5001 106215 5002 60490.79 5003 2564.59 5004 182390.9 5050 148759.3 and so on with all codes

Requirement is to get the ratio at the level of GL code for merchandise or payroll / Total revenue. For Example,

For merchandise , gl code 5000, ratio = 691249.34/650000.I have attached the sample data as well.

JYI, the amounts are coming from a single measure and from same source

Please let me know how I accomplish this scenario.

• ###### 1. Re: Row Level Ratio from the same dimension in

Hi Lolakshi,

Thanks

Deepak

• ###### 2. Re: Row Level Ratio from the same dimension

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

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

I am Sorry. I just edited my post again. Is it okay now?

• ###### 5. Re: Row Level Ratio from the same dimension

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

• ###### 6. Re: Row Level Ratio from the same dimension

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

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

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

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

• ###### 10. Re: Row Level Ratio from the same dimension

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

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

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

• ###### 13. Re: Row Level Ratio from the same dimension

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

1 of 1 people found this helpful
• ###### 14. Re: Row Level Ratio from the same dimension

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 ....

1 2 Previous Next