Calculating costs between two tables

[Syndicate_Admin]

Hello

I want to calculate the total loss/theft cost by multiplying the incident units by the average unit cost of sales of the same Product–POS–Date.

The FactSales (has costs per day) and FactIncident (has reported units) tables are not directly related to each other, but both are related to DimProduct (ProductID) and POS/Branch (BranchID).

Both have the Date column (at the day level).

Relevant columns: ProductID, BranchID (POS), Date.

The following are the measurements I am using:

Total Cost =
SUMX(
SUMMARIZE(
FactIncident,
FactIncident[ProductID],
FactIncident[Date],
FactIncident[POS]
),
[Incident Units] * [Average Unit Cost]
)

Incident Units =
SUM ( FactIncident [QuantityUnits] )

Average Unit Cost =
DIVIDE ( [Cost of Sales], [Sale Units] )

Cost of Sales =
SUM ( FactSales[cost] ) )

Selling Units =
SUM ( FactSales[SaleUnits] )

The problem I'm having is that when I calculate the data, it is not generating the total, although if I check it by row the data is fine.

I share with you the semantic model.

I thank you for your help or guidance on how I should generate this measurement between different tables.

POS Note = BranchID

[v-pnaroju-msft · Accepted solution]

Thankyou, @pankajnamekar25, for your response.

Hi Syndicate_Admin,

We appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

We aggregated sales data to calculate the average cost per product, branch, and date. For each incident row, we located the corresponding average cost and then multiplied the incident units by that average cost. Finally, we summed these values to obtain the total.

Please find attached a screenshot and a sample PBIX file that may assist in resolving the issue:

We hope the information provided helps to address your concern. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[pankajnamekar25]

Hello @Syndicate_Admin 

Try this DAX

Total Cost =
SUMX (
FactIncident,
FactIncident[QuantityUnits] *
CALCULATE (
DIVIDE (
SUM ( FactSales[Cost] ),
SUM ( FactSales[SaleUnits] )
),
TREATAS ( VALUES ( FactIncident[ProductID] ), FactSales[ProductID] ),
TREATAS ( VALUES ( FactIncident[POS] ), FactSales[BranchID] ),
TREATAS ( VALUES ( FactIncident[Date] ), FactSales[Date] )
)
)