Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
1 year ago
Solved

Calculating costs between two tables

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 FactInci...
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    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's avatar
pankajnamekar25
Icon for Super User rankSuper User
1 year ago

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] )
)
)