Spotfire-计算小计的百分比 [英] Spotfire - Finding Percentage of Subtotals

问题描述

我正在尝试创建一个如下所示的交叉表

I'm trying to turn a cross table that looks like this

插入一个表格，该表格显示每个组的小计和百分比，如下例所示

into a table which shows the subtotals and percentage over each Group like the example below

其中百分比是每个产品的销售额除以每个组的总销售额，因此对于产品A = 20 /（20 + 40 + 30）= 22％

Where the percentage is the sales of each product divided by the total sales in each group, so for Product A = 20 / (20+40+30) = 22%

到目前为止，我已经设法使用Spotfire内置的小计功能和以下表达式几乎实现了我想要的表

So far, I've managed to use Spotfire built-in subtotal function and the following expression to almost achieved the table I want

Sum([Sales) / Sum([Sales]) OVER (Intersect(Parent([Axis.Rows]),All([Axis.Rows])))

，但是唯一的问题是我的小计行的百分比似乎不等于100％，而是小计销售额除以所有组的总计，即 90 /（90 + 140）= 39％

but the only problem is that the percentage for my subtotal row doesn't seems to equal to 100%, instead it is taking the subtotal sales divided by the grand total for all groups, meaning 90 / (90+140) = 39%

有人可以告诉我我哪里出错了，我该如何解决？谢谢

Can anyone advise me where did I go wrong and how do I resolve this? Thanks

推荐答案

您在任何地方都没遇到问题，但是小计并不能确定您想要的方式。小计由Spotfire根据基础分组数据自动计算。因此，在这种情况下，小计将用于层次结构（组）的第一个节点。即第1组和第2组。您将无法使用当前的布局进行更改。如您所知，小计是总计的一部分。总计适用于整个交叉表，无论您的数据是什么，总数都是100％。因此，两个或多个小计的总和> 100％是不可能的。您的小计是根据此公式计算得出的值的总和：

You didn't go wrong anywhere but your subtotals aren't going to work out how you want. The Subtotals are calculated by Spotfire automatically on the underlying grouped data. So in this case, the subtotal is going to be for the first node of your hierarchy (group). i.e. Groups 1 and 2. You aren't going to be able to change it with your current layout. As you know, subtotal is a portion of the grand total. Grand total is for the entire Cross Table, which will be 100% no matter what your data is. Thus, it's impossible for the sums of 2 or more subtotals to be > 100%. Your subtotal is the SUM of the values calculated off of this formula:

Sum([Sales]) THEN [Value] / Sum([Value]) OVER (All([Axis.Rows])) as [% of Total]

这篇关于Spotfire-计算小计的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！