Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Multiply a column from a table with another column from another table for specified year and company

Jump to Best Answer
  • 1.  Multiply a column from a table with another column from another table for specified year and company

    Posted May 29, 2022 04:45 PM
    Hello guys

    Please help me
    I have 2 tables, one containing services and another costs
    I want to multiply  column quantity with Price if the company codes are equal and the year of date = year of Price date.
    Thank you

    TABLE SERVICES COSTS
    Company Service name Date Quantity Company Price date Price
    ADM Service1 10/10/21 100 ADM 1/1/2021 50
    ADB Service2 10/11/21 150 ADB 1/1/2021 60
    ADD Service3 10/12/21 256 ADD 1/1/2021 70
    ADM Service4 10/13/21 456 ADM 1/1/2022 80
    ADB Service5 10/14/21 789 ADB 1/1/2022 90
    ADD Service1 10/10/22 200 ADD 1/1/2022 100
    ADM Service2 10/10/22 250
    ADB Service3 10/10/22 356
    ADD Service4 10/10/22 556
    ADB Service5 10/10/22 678


    ------------------------------
    Mihai Stanca
    Acasa
    ------------------------------


  • 2.  RE: Multiply a column from a table with another column from another table for specified year and company
    Best Answer

    Posted May 31, 2022 02:15 AM
    Hi,
    First you have to make relation between those data table
    1. create table company
    2. create table date
    3. link them to data tables
    then make some measure
    1. total quantity
    2. total price

    after that you can multiply them as you need

    ------------------------------
    Dili Timor
    12510
    ------------------------------



  • 3.  RE: Multiply a column from a table with another column from another table for specified year and company

    Posted May 31, 2022 02:22 AM
    After that you can create matrix with year and company as row and "quantity*price" as column/value

    ------------------------------
    Dili Timor
    12510
    ------------------------------