Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Multiply 2 Columns By Row Context

Hello, 

I tried to search in another posts to find solution without success...

 

I have Data Model with 2 Tables.

Table1 Contains Sales Data, Table2 Contains Sales Goals.

Every Product In Table1 belongs to a ProductGroup.

There Is Relationship Between Tables Based on The ProductGroup Column.

 

I Want to create a measure that multiply the Total SalesAmount From Table1 in the SalesGoal From Table2 by their ProductGroup Context, For Example:

I Want the apple&avocado&lemon Total SalesAmount to be Multiplied in 10% Because this is their Product Group Goal.

Same Logic For The tomato&apricorn and etc.

 

Thanks In Advance

 

Below i Attached A Screenshot:

https://imgur.com/E02ZsFg 

 
 

 

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    Based on the data provided by you, I have created the following DAX to get the expected results:

    Measure =
VAR sum_group =
    CALCULATE (
        SUM ( Table1[SalesAmount] ),
        ALLEXCEPT ( Table1, Table1[ProductGroup] )
    )
RETURN
    CALCULATE (
        sum_group * MAX ( Table2[ProductGroup Sales Goal] ),
        CROSSFILTER ( Table2[ProductGroup], Table1[ProductGroup], BOTH )
    )

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Based on the data provided by you, I have created the following DAX to get the expected results:

    Measure =
VAR sum_group =
    CALCULATE (
        SUM ( Table1[SalesAmount] ),
        ALLEXCEPT ( Table1, Table1[ProductGroup] )
    )
RETURN
    CALCULATE (
        sum_group * MAX ( Table2[ProductGroup Sales Goal] ),
        CROSSFILTER ( Table2[ProductGroup], Table1[ProductGroup], BOTH )
    )

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.