Measure sum problem

[Anonymous]

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
C0001	10	A0001	2	10
C0001	20	A0002	3	6
C0001	30	A0003	1	20
C0002	10	A0001	10	50

DIM_ARTICLES

Article	Family	Price_ex_works
A0001	AAA	2
A0002	AAA	1
A0003	BBB	4

So, I am trying to measure the % margin of each family. I have two different measures :

- Total_Price_ex_works = calculate(SUM(DIM_ARTICLES[Price_ex_works])*SUM(FT_COMMAND[Qty]))

- % Margin = FORMAT((sum(FT_COMMAND[Total_Price])-(sum(DIM_ARTICLES[Price_ex_works])*sum(FT_COMMAND[Qte])))/sum(FT_COMMAND[Total_Price]);"Percent")

But when I build a table I have this result :

Familly	Article	Total_Price	Qté	Price_ex_works	Total_Price_ex_works	% Margin
AAA	A0001	60	12	2	24	60%
AAA	A0002	6	3	1	3	50%
TOTAL AAA		66	15	3	45	32%

But i should have :

Familly	Article	Total_Price	Qté	Price_ex_works	Total_Price_ex_works	% Margin
AAA	A0001	60	12	2	24	60%
AAA	A0002	6	3	1	3	50%
TOTAL AAA		66			27	59%

The problem is, the system is suming the total, but it shouldn't.

I hope you can help me, i'm starting on Power BI, and unfortunately some basic function are hard for me to use.

Thank you.

Regards,

RomainH

[Anonymous]

Hello, thank you so much, it works !

[Ashish_Mathur]

Hi,

You may download my solution PBI file from here.

Hope this helps.

[v-piga-msft · Accepted solution]

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