Powerpivot - creating ratios from a single fact table

Question

I have a powerpivot table that looks like this... all "facts" are in a single table with the Information column used to segregate:

Information | Year | Amount
Expense       2010   1000000
Units Sold    2010   50000
Expense       2011   2000000
Units Sold    2011   125000

I would like to be able to calculate various ratios. This seems like a straightforward need, but I've not been able to figure it out or find a solution online.

For example...

• Expense / Units Sold for 2010, 2011 or combined (based on filters applied in Excel)
• ..... 2010 = 1000000/50000 = 20; 2011 = 2000000/125000 = 16; all time = 3000000/175000 = 17.14 ETC.

In reality there are many more dimensions (region, location, month, etc.) and other Information types (headcount, square footage, etc.), but only three or four denominators, so I am hopeful the solution will scale out.

How can I do that in Powerpivot? I can calculate the denominator using (as an example) CALCULATE(SUM(fact[amount]),fact[information]="Units Sold")), but cannot get that denominator applied against all numerators.

x x x x

Adding follow up based on the answer provided below...

Issue #1: The solution provided below works when I am looking at Total Expenses, but in reality the Expense line will be multiple lines (Salary, Benefits, Rent, etc.) and I need to be able to calculate each per Unit Sold. The solution doesn't seem to allow for this.

Issue #2: The fact table currently has four different information types ... Expenses (broken out as noted above), Units Sold, Headcount and Square Footage. What I would like to be able to do is have Expenses per Until Sold, Headcount per Unit Sold, etc. In other words, a denominator that can be applied against all rows which then can be summed in the pivot table easily. The solution takes everything that is not Units Sold, sums it and then divides by Units Sold... blending Expenses, Headcount and Square Footage.

Thanks again for the assistance.

Answer 1

SumAmt:= SUM( FactTable[Amount] )

UnitsSoldDenominator:=
DIVIDE(
    CALCULATE(
        [SumAmt]
        ,FILTER(
            VALUES( FactTable[Information] )
            ,FactTable[Information] <> "Units Sold"
        )
    )
    ,CALCULATE(
        [SumAmt]
        ,FactTable[Information] = "Units Sold"
    )
)

This will give the behavior you want.

It's worth taking some time to explain why our filter arguments to CALCULATE() are different in the numerator and the denominator.

CALCULATE() with a literal predicate, like we see in the second argument to DIVIDE() is implicitly rewritten to the following:

CALCULATE(
    [SumAmt]
    ,FILTER(
        ALL( FactTable[Information] )
        ,FactTable[Information] = "Units Sold"
    )
)

FILTER() simply iterates over the table passed to it in argument one and returns the rows from that table for which argument two returns true.

With the implicit ALL() there, then there is no context preserved from the pivot table.

VALUES() evaluates the table or column reference in filter context. Thus when we evaluate VALUES( FactTable[Information] ), we get back only the value in context in the pivot table. In any given year, we get back every label that exists in that year, at the grand total we get every label. At the "Units Sold" level, we get back nothing, because we're filtering that out.

Here's an image of a pivot table with the expected behavior based on your sample data and this measure.

Edit for dimension table

UnitsSoldDenominator:=DIVIDE(
    CALCULATE(
        [SumAmt]
        ,FILTER(
            VALUES( DimInformation[Information] )
            ,DimInformation[Information] <> "Units Sold"
        )
    )
    ,CALCULATE(
        [SumAmt]
        ,DimInformation[Information] = "Units Sold"
    )
)

This depends on an active relationship being defined between FactTable and DimInformation. I've defined mine on a numeric [InformationKey].

The image below includes my sample data based on what you provided in the original question, plus a mocked up dimension inferred from the same. The pivot table is shown along with the expanded field list. The relationships in my model are displayed in the manage relationships dialog in the Power Pivot window. I am seeing the same behavior as writing the original measure I provided against FactTable[Information] with the new version written against DimInformation[Information].

Please let me know if your model is configured the same and you are unable to reproduce this.

Edit 2 for hierarchy

UnitsSoldDenominator:=DIVIDE(
    CALCULATE(
        [SumAmt]
        ,FILTER(
            DimInformation
            ,DimInformation[Information] <> "Units Sold"
        )
    )
    ,CALCULATE(
        [SumAmt]
        ,ALL( DimInformation )
        ,DimInformation[Information] = "Units Sold"
    )
)

Here we've added a call to ALL( DimInformation ) in the denominator. This is because we will have filter context in the pivot table from DimInformation[InformationGroup] and DimInformation[Information]. The filter predicate in CALCULATE(), the DimInformation[Information] = "Units Sold", implicitly strips filter context that exists on DimInformation[Information], but when we put DimInformation[InformationGroup] into the pivot table, it provides a new set of filter context (all filter contexts are evaluated in a logical and).

Thus we're looking for (in the example in the image below) DimInformation[InformationGroup] = "Expense" (this is the context which is provided by the pivot table) && DimInformation[Information] = "Units Sold". This is not possible in our data so the denominator would be blank.

Thus we strip the filter context from all of DimInformation, and then evaluate our literal predicate.