Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hello, thank you so much, it works !
Hi,
You may download my solution PBI file from here.
Hope this helps.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |