Copying Excel Formulas with Parameters for interactive dashboard

[Anonymous]

Fabric Community,

I have an excel workbook that I'm looking to effectively duplicate into an interactive dashboard. There is one column that is causing trouble. Lets call the table 'Construction Unit Cost'. it contains [Functional Area], [Item Description], [Unit of Measure], [Unit Cost], and two calculated columns, one with quanities and the row calculation of unit cost * quantity = Total Cost column. The problem for me lies in the fact that there are about 15 different descriptions and 6 functional areas leading to around 20 unique geometric formulas, each of which rely on slicer parameters of height, length and width to calculate surface areas and volumes. For this reason I have been avoiding calculated columns.

My methodoligy attempts have included creating many variables in a measure that precalculate the VAR __height = LOOKUPVALUE('Height'[Value], 'Height'[Value], MAX('Height'[Value])) and similarly for __width and __length use these variables to calculate variables for __surface_area and __volume. I then have variables one for each of the quantity calculations. And i have a switch statement using

__switch = SWITCH(TRUE(), CONTAINS('Construction Unit Cost','Construction Unit Cost'[Item Description],"Item1"), __item_1_qty * __unit_cost,...)

and finally I try to sumx this over the my table. My problem typically comes to the sum has either been yielding 15 * Item1 Cost or Sum(unit cost) * sum(all item qty) or sum(unit cost) * item 1 qty while the table reports each line item with the correct cost calculated. There is also another problem where changing the length changes some of the individual costs but not the total cost, while height and width change everything including the total. 

This has been driving me a bit crazy with the order of operations failing even though I thought I had it correct:

calculate row level quantities, row level unit cost * quantity, sum the result as a column, report total cost

Any advice will be helpful for the DAX or reworking the model conceptually, I can be convinced to create custom calculation columns as long as parameters will still be viable. I will likely just roll everything up into a single calculation and hard code the unit cost, but then the length acting as a completely disconnected variable still remains.

[Anonymous]

Hi, @Anonymous 

Given the complexity of the calculations and the dynamic nature of the parameters (height, length, width), I recommend using DAX measures instead of calculated columns in your scenario. Metrics are dynamic and respond to slicer selection in real time, which is what you want from an interactive dashboard element. 

The following DAX is an example that I hope will help you:

Total Cost Measure = 
VAR __height = LOOKUPVALUE('Height'[Value], 'Height'[Value], MAX('Height'[Value]))
VAR __width = LOOKUPVALUE('Width'[Value], 'Width'[Value], MAX('Width'[Value]))
VAR __length = LOOKUPVALUE('Length'[Value], 'Length'[Value], MAX('Length'[Value]))
-- Add your __surface_area and __volume calculations here
VAR __totalCost = SUMX('Construction Unit Cost', [Your Item Cost Measure])
RETURN
__totalCost

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

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