Ratio Issue
I have a PivotTable with my Branches as Columns and “Chart Of Accounts” on my rows(Revenue, Labor, Direct Costs, etc.). My Values are my “Actual Amounts” and “Ratio”
I am calculating the ratio of dollars spent on Labor, Direct Costs, etc in relation to the Revenue of each branch(The revenue Chart of Account line will be 100% as the Revenue is 100% of the Revenue). I then want to the Totals to do that same formula. Instead, it is seeing the ratios for each Branch, and then just summing those up, so instead of 100%, it returns 400%(4 branches).
I am calculating the Total Revenue at each branch using sumover(VARIABLE, [Branch], PRE_AGG). I think take the Chart of Account Amount / Total Revenue.
Hi @JCookGTX ,
Welcome to the Community!
What value are you expecting in totals and how are you deriving it? Please help with an example so that we can try modifying the calculations.
The reason it is summing up is because the current aggregation set for ratio is sum. Also Revenue is just another row item for the table. Although conceptually you are not treating it as a record entry, all the numbers are getting aggregated following the aggregate function we are mentioning in the visual.
Thanks,
Prantika
I am expecting the Total Ratio at the Revenue level to be 100%, as the calculation is essentially Revenue / Revenue. I am wondering if there is a way for the Totals to continue using my formula, rather than simply summing across the table.
I’m essentially recreating an Income Statement. The end goal is to be able to see what percentage of revenue was spent on Labor, Direct Costs, etc. We want to understand that percentage at the Branch level, but also in total for the entire company.
I apologize, I am by no means a data expert. What example data would help you?
Thank you for your response.
Lets take this example. The Ratio already shows 100% against Revenue.
Do you expect to see -87% instead of 13% in the total? Is the GL Detail Amt total matching your expected value?
If not, what values are you expecting under total? -8700 and -87%?
The summation of the Rows is working correctly. The 13% is correct.
The summation of the Columns is the issue. We expect the totals to be the following:
|
|
Totals
|
|
Revenue
|
49000
|
100%
|
|
Labor
|
-41200
|
-84%
|
|
Direct Costs
|
-3200
|
-6.50%
|
|
Total
|
4600
|
9.40%
|
Ok, so instead of aggregating in visual, if you try aggregating within calculation.
Something like : sum({GL Detail Amt}) / sum({Total Revenue})
