Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Gérard Ducouret]

Hi,
I’m trying to understand how the Power BI Desktop tables are working.
The “Unit Price” field is displayed in 2 tables : “Product” and “Sales“.
In a matrix, I have the Product name column .
I drag n drop the “Unit Price” from “Sales” and I replace Count by Sum: it displays $2398,80
I drag n drop the “Unit Price” from “Product” and I replace Count by Sum: it displays $199,9
I checked that in the data base the true “Unit Price” is $199,9. So “Sales” displays a “Unit Price” 12 times too big.
I checked that 12 is not the Quantity : The Quantity is 16.
12 is the number of order rows…..
What is the logic in these calculations ?
Thanks for your help

——————————
Gérard Ducouret
——————————

[Cekou C.]

Hi

I assume Sales is on the many side of the relationship with the product table with, I assume, a one-way relationship.

I think it has something to do with the fact that Sales table cannot filter Back Category from the Product table.

Kr,

——————————
Cekou C.
Accounting Supervisor & PBI Enthusiast
——————————
——————————————-

[Audrey Abbey]

When you use the Unit Price from Sales, and select SUM as the aggregator, it is adding up every instance of the unit price found in sales. 
So if you had 12 sales records for the product, you would get 12 * the unit price. 

You can use the unit price from sales, but you would need to use MIN or MAX (they should be the same) for the aggregation instead of SUM. 
Or, if you have multiple prices for a given product, you can use AVERAGE.

If you use the unit price from Product, that Product only occurs once in that table. So, there is nothing to SUM, and you get the correct 199.99

Does that make sense?

——————————
Audrey Abbey
SR. BI Developer/Analyst
LeapFrogBI
Portland OR
——————————
——————————————-

[Gérard Ducouret]

Thanks Audrey,
Yes, that make sense for me now !

——————————
Gérard Ducouret
——————————
——————————————-