Forum Discussion

LoNiCho's avatar
LoNiCho
Regular Visitor
8 years ago
Solved

lookup

I have a database with costs related to products Product  SubProduct            Manufact_Cost      Package_Cost  Delivery_Cost 1                     A                                15             ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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
v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

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's avatar
LoNiCho
Regular Visitor
8 years ago

Thank you Maggie that looks great.