Calculate percentage between 2 values in 2 different tables (Query out of ERP)

[HansDW]

Hello

We have query what gets all invoiced lines in a period and our standard sales price list.

Now i want to see per invoice line the discount vs the general price list.

General Pricelist
Article	Price
Article 1	10,6
Article 2	12
Article 3	5

Invoices
Invoices	Article	UnitPrice
1	Article 1	10
1	Article 3	11,5
1	Article 2	4,8
2	Article 3	4,9
2	Article 2	11
3	Article 1	9
3	Article 3	4

[Anonymous]

Hi @HansDW ,

Any updates? Please feel free to ask.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @HansDW ,

To my knowledge, such error occurs when your [pricevar] is 0 or blank, which means it did not return correct values.

And since I didn't see [ItemCode] in your screenshot, I need more details to clarify your scenario.

Please kindly take a look at the blogs to know

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @HansDW ,

Please try:

Discount = 
var _price=CALCULATE(SUM('General Pricelist'[Price]),FILTER('Invoices',[Article]=MAX('General Pricelist'[Article])))
return (_price- SUM(Invoices[UnitPrice]))/_price

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HansDW]

Hi eyelyn

Much appreciated your help. But in my real data I get -Infinity as a result.

Discount% = VAR pricevar = Calculate(SUM(PricelistEUR[EurPrice]), Filter('FullSalesData',[ItemCode]=MAX(PricelistEUR[ItemCode]))) return (pricevar - SUM(FullSalesData[UnitPrice]))/pricevar

Best regards

Hans

[Anonymous]

Hi @HansDW ,

Is there any update?

You could use the following syntax to create a measure:

% = SUM(Invoices[UnitPrice]) /  CALCULATE(SUM('General Pricelist'[Price]),FILTER('Invoices',[Article]=MAX('General Pricelist'[Article])))

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HansDW]

Thanks Eyelyn

But this is not the discount.

[Line 1] This is what 10 is of 10.60€. Should give 5.66%

Hans

[rohit_singh]

Hi @HansDW ,

Please try the steps below:

1) Load the following tables into the report view

Table 1 : General Pricelist

Table 2 : Invoices

2) Create relationship between the two tables using Article column as the key

3) Add the below columns to the table view. You will now be able to see both general and discounted prices on the same row.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[HansDW]

Hi Rohit_Sing

Step one and two was already OK, Adding the column as is, is not possible. Its a continious update out of OUR ERP.  I could add the pricing table to the invoice table due to aitchandak related solution. it's multiple in invoice table., So one in pricelist can have 100 different pricins in invoice.

[amitchandak]

@HansDW , if there is a relation between table 1 and table 2 (1 to Many) . then you can use related(table1[Price]) in a column or measure from table 2 as per need

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[HansDW]

Relation was already setup. Added the relate collmn. Now trying to to the percentage calculation 😉 Will come back.