Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |