Forum Discussion
Measure sum problem
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
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
3 Replies
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
