cancel
Showing results for
Did you mean:
Anonymous
Not applicable

Using Measures and Calculated Columns Together

I am looking to calculate a total potential exposure by going through a series of calculations, and I keep running into 3 pain points. Every column up to "Days Remaining 2" does not use any calculations. The only column (calculated) from a different table is "Sales Rate". I am pretty new to using Power BI, so I apologize in advance if my notes below are not easy to follow. Happy to help clarify wherever I can.

Problem 1 (A) - "Days Remaining 2" column should be calculated by taking "Item Shelf" multiplied by SELECTEDVALUE(ShelfRemaining) - this yields an error. When I replace the "SELECTEDVALUE(ShelfRemaining)" with an arbitrary number such as 0.5 as shown in the image, it calculates correctly but I really would like this do be changeable based on user-selected shelf life % remaining. This is currently a calculated column.

Problem 2 (B) - "Sales Rate" is calculating correctly as is. It is adding up total sales volumns between the two dates in the slicer above specific to that row's distribution center, item ID, and vendor. My concern is that because it is a measure rather than a calculated column, when I get to the point of adding up "Total Exposure 2" and graphing these in the three visuals above by their respective axis (e.g. vendor, distribution center, etc.), it will calculate based on total sales for that vendor, rather than filtering by vendor, distribution center, and item ID first, then calculating total exposure for each line item, then taking a sum of total exposure.

Problem 3 (C) - "Cases Used Before Expiration 2". These calculations are just flat out wrong and I am unable to piece together why. Basically what this column needs to do is take the MIN between (1) "PO Quantity (Received)" and (2) "Sales Rate" multiplied by "Days Remaining 2"

Calculated Column Formulas:

Days Remaining 2 = PO[Item Shelf]*0.5
Expiration Date 2 = [PO Date (Actual Receive)]+PO[Days Remaining 2]
Cases Used Before Expiration 2 = IF(PO[Days Remaining 2]*CALCULATE([Sales Rate])>[PO Quantity (Received)],[PO Quantity (Received)],IF(CALCULATE([Sales Rate])=0,0,PO[Days Remaining 2]*CALCULATE([Sales Rate])))
Cases Expired 2 = [PO Quantity (Received)]-[Cases Used Before Expiration 2]
Total Exposure 2 = [Cases Expired 2]*[Landed Cost]

Measure
Sales Rate = CALCULATE(Data_SalesBaseline[Baseline Sales Rate (Daily)],FILTER(Data_SalesBaseline,Data_SalesBaseline[DCDCItem#]=SELECTEDVALUE(PO[DCDCItem#])))

--where--
Baseline Sales Rate (Daily) = CALCULATE(SUM(Data_SalesBaseline[Cases Sold]),FILTER(Data_SalesBaseline,Data_SalesBaseline[InvoiceDate]>=Input_Baseline[Input_Baseline_Min]),FILTER(Data_SalesBaseline,Data_SalesBaseline[InvoiceDate]<=[Input_Baseline_Max]))/(DATEDIFF([Input_Baseline_Min],[Input_Baseline_Max],DAY)+1)
Input_Baseline_Max = MAX(Input_Baseline[Date])+0.9999
Input_Baseline_Min = MIN(Input_Baseline[Date])

2 REPLIES 2
Community Support

Hi @Anonymous

You might consider creating pbix file that will contain some sample data, upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

Announcements