Forum Discussion

tmears's avatar
tmears
Icon for Helper III rankHelper III
7 years ago
Solved

need some help on a calculated column between 2 Seperate tables which have no direct relationship

Hi Wonder if any one out there could help me.....  I'm trying to perform a calculation over two unrelated tables, which are connected through a third table   Table 1 has individual transaction row...
Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi tmears ,

    If I havne't misunderstood your requirement, you want to add the sum of 'sales table'[Profit] to target table based on the current month and nominal. For example, the following item:

    You want to get the sum of 'sales table'[Profit] in whole 2019 May, and the nominal is 4001.

    In this scenario, we can use the following DAx query:

    Amount by Revenue (sum) =
CALCULATE (
    SUM ( 'sales table'[Profit] ),
    FILTER (
        ALL ( 'sales table' ),
        'sales table'[DocumentHeaders.Created].[Month]
            = EARLIER ( 'target table'[Date].[Month] )
            && 'sales table'[Full Nominal List] = EARLIER ( 'target table'[Nominal] )
            && 'sales table'[DocumentHeaders.Created].[Year]
                = EARLIER ( 'target table'[Date].[Year] )
    )
)

    The result will like below:

    Best Regards,

    Teige

tmears's avatar
tmears
Icon for Helper III rankHelper III
7 years ago

TeigeGao  Please find enclosed a sample PBIX File.

 

Bascially i am trying to add a calculated column in the target table of the SUM by month of the profit column in the Sales Table.Link to Sample PBIX

 

Many thanks

T

TeigeGao's avatar
TeigeGao
Icon for Solution Sage rankSolution Sage
7 years ago

Hi tmears ,

If I havne't misunderstood your requirement, you want to add the sum of 'sales table'[Profit] to target table based on the current month and nominal. For example, the following item:

You want to get the sum of 'sales table'[Profit] in whole 2019 May, and the nominal is 4001.

In this scenario, we can use the following DAx query:

Amount by Revenue (sum) =
CALCULATE (
    SUM ( 'sales table'[Profit] ),
    FILTER (
        ALL ( 'sales table' ),
        'sales table'[DocumentHeaders.Created].[Month]
            = EARLIER ( 'target table'[Date].[Month] )
            && 'sales table'[Full Nominal List] = EARLIER ( 'target table'[Nominal] )
            && 'sales table'[DocumentHeaders.Created].[Year]
                = EARLIER ( 'target table'[Date].[Year] )
    )
)

The result will like below:

Best Regards,

Teige