Forum Discussion
Need help with with tabel Power Query
Hi all,
We've got an excel with all items. This file is being refreshed automatically from our database.
In our database, we have let's say 10 items with salesprice 500, costprice 500 and thus margin 0. At the end of the month there will be an additional invoice (manually) and the we charge 15% margin.
In excel I've added an If function --> if ... article x then add 15% margin. I can do that by year (now).
In power BI I still have 0 as margin. It is possible for me to add an additional column or a addition to a measure to add the 15% (on some products), but I want to have that sort of dynamically, because this can change over year, but also over month / day etc... I cannot make columns / measures with if this, then, but if that, then and so on...
Do I need to make an extra tabel (maybe in excel, and update that manually when something changes)? If yea, how do I have to format / make my DAX measure (or calculated column in power query?!), in a way that the formula looks to the invoice date and what "increment category" it should take?
Can someone help? Thanks in advance!
Hans
Hi hansreivers TRy this
In Power Query, create a conditional column:
Margin = if [Article] = "x" then [Costprice] * 0.15 else 0In Power BI, add a calculated column:
TotalPrice = [Salesprice] + [Margin]Use a mapping table for dynamic updates, or a DAX measure:
DynamicMargin = IF(SELECTEDVALUE('Table'[Article]) = "x", SUM('Table'[Costprice]) * 0.15, 0)If this post helped please do give a kuods and accept this as a solution
Thanks In AdvanceHi hansreivers Then you can add a log table with Article, Effective Date, and Increment columns. Load it into Power BI and create a dynamic margin measure using DAX:
DynamicMargin = VAR Increment = CALCULATE( MAX('LogTable'[Increment]), FILTER( 'LogTable', 'LogTable'[Article] = 'SalesTable'[Article] && 'LogTable'[Effective Date] <= 'SalesTable'[Date] ) ) RETURN 'SalesTable'[Costprice] * Increment
6 Replies
Hi hansreivers TRy this
In Power Query, create a conditional column:
Margin = if [Article] = "x" then [Costprice] * 0.15 else 0In Power BI, add a calculated column:
TotalPrice = [Salesprice] + [Margin]Use a mapping table for dynamic updates, or a DAX measure:
DynamicMargin = IF(SELECTEDVALUE('Table'[Article]) = "x", SUM('Table'[Costprice]) * 0.15, 0)If this post helped please do give a kuods and accept this as a solution
Thanks In AdvanceHi Akash_Varuna and the community,
That could work, but then I have to make a "manually measure" for all these exeptions and I want to have a sort of "log tabel" where I can / need to refer to, and I want to make this dynamically with one measure / column for the margin.
When I for example for article X have 2 sales;
01-05-24 --> the increment should be 15%
01-05-25 --> the increment should be 17,50%
Sale product Y dd 05-03-25 the increment should be 15% of € 350 because of the change in price per 01-01-25
Thanks in advance,
Hi hansreivers Then you can add a log table with Article, Effective Date, and Increment columns. Load it into Power BI and create a dynamic margin measure using DAX:
DynamicMargin = VAR Increment = CALCULATE( MAX('LogTable'[Increment]), FILTER( 'LogTable', 'LogTable'[Article] = 'SalesTable'[Article] && 'LogTable'[Effective Date] <= 'SalesTable'[Date] ) ) RETURN 'SalesTable'[Costprice] * Increment
Hi hansreivers,
Thanks Akash_Varuna for Addressing the issue.
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay PabbuHi hansreivers,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Regards,
Vinay PabbuHi @hansreivers,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay Pabbu
