Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure sum problem

[ Spoiler ] Hello,   Maybe you can help me ?   I have two tables in my model. One for command and one for product repository.   FT_COMMAND Command_Number Line Article Qty Total_Price ...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Anonymous,

     

    You'd better create the calculated column in table DIM_ARTICLES to calculate the Total_Price_ex_works with the formula below.

     

    Column =
CALCULATE ( SUM ( 'FT_COMMAND'[Qty] ) * SUM ( 'DIM_ARTICLES'[Price_ex_works] ) )

    Then you could create the measure like this.

    % Margin =
FORMAT (
    DIVIDE (
        CALCULATE ( SUM ( 'FT_COMMAND'[Total_Price] ) )
            - CALCULATE ( SUM ( 'DIM_ARTICLES'[Total_Price_ex_works] ) ),
        CALCULATE ( SUM ( 'FT_COMMAND'[Total_Price] ) )
    ),
    "Percent"
)

    Here is the output.

     

     

    Hope this can help you!

     

    Best Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi Anonymous,

 

You'd better create the calculated column in table DIM_ARTICLES to calculate the Total_Price_ex_works with the formula below.

 

Column =
CALCULATE ( SUM ( 'FT_COMMAND'[Qty] ) * SUM ( 'DIM_ARTICLES'[Price_ex_works] ) )

Then you could create the measure like this.

% Margin =
FORMAT (
    DIVIDE (
        CALCULATE ( SUM ( 'FT_COMMAND'[Total_Price] ) )
            - CALCULATE ( SUM ( 'DIM_ARTICLES'[Total_Price_ex_works] ) ),
        CALCULATE ( SUM ( 'FT_COMMAND'[Total_Price] ) )
    ),
    "Percent"
)

Here is the output.

 

 

Hope this can help you!

 

Best Regards,

Cherry