cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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 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

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Measure sum problem

Hi @RomainH,

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.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super Contributor

## Re: Measure sum problem

Hi @RomainH,

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.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User IV

## Re: Measure sum problem

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Measure sum problem

Hello, thank you so much, it works !

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.