lookup

[LoNiCho]

I have a database with costs related to products

Product  SubProduct            Manufact_Cost      Package_Cost  Delivery_Cost

1                     A                                15                      20                     20

1                     B                                16                      25                     22

1                     C                                14                      22                     28

2                     A                                16                      10                     20

2                     B                                12                      15                     25

2                     C                                11                      10                     20

3                     A                                25                     14                     10

3                     B                                27                      15                     14

3                     C                                22                      15                     10

But I've been asked to have a filter by the Cost Type, so selecting Manufacturing, Package and Delivery, so one or multiple of these can be selected and displayed, summing over product or subproduct.

I have set up an indexed table called Cost Type to use as a filter but how do I lookup from my Fact table to my cost type table .

Index  CostType

1         Manufact_Cost

2         Package_Cost  

3         Delivery_Cost

Many thanks,

LoNiCho

[v-juanli-msft · Accepted solution]

Hi @LoNiCho

To have a filter by the Cost Type, you create the cost type table and you need relate the Fact table with the cost type table.

This also made me stuck in, fortunately, I find another method to achieve your goal.

The formula below will create a new table based on the fact table you provided, then we can have a filter by the Cost Type.

Table =
VAR table1 =
    SUMMARIZE (
        Sheet1,
        [Product ],
        [SubProduct],
        [Delivery_Cost],
        "Cost Type", IF ( [Delivery_Cost] <> BLANK (), "Delivery_Cost" )
    )
VAR table2 =
    SUMMARIZE (
        Sheet1,
        [Product ],
        [SubProduct],
        [Manufact_Cost],
        "Cost Type", IF ( [Manufact_Cost] <> BLANK (), "Manufact_Cost" )
    )
VAR table3 =
    SUMMARIZE (
        Sheet1,
        [Product ],
        [SubProduct],
        [Package_Cost],
        "Cost Type", IF ( [Package_Cost] <> BLANK (), "Package_Cost" )
    )
RETURN
    UNION ( table1, table2, table3 )

Best Regards

Maggie

[LoNiCho]

Thank you Maggie that looks great.