Issues with Merged Column

The NaN or Infinity errors are happening because your calculated column formula Cost_Wdc_VP = VP_JobCost[Cost] / [Total Wdc3] is trying to divide a single row's Cost by the grand total SUM of all watts in your entire dataset, which evaluates to zero or blank in a row-by-row context.

The best and most efficient way to solve this is to perform the entire calculation as a single measure.

Instead of creating a column and a separate Wdc measure, combine all the logic into one flexible measure. This single measure will work correctly in your heatmap, tables, and any other visual.

• Delete both the Cost_Wdc_VP column and the Total Wdc3 measure to avoid confusion.
• Create a new measure with the following DAX formula:

Cost per Wdc =
-- First, calculate the total cost in the current context (e.g., for a single project in a matrix row)
VAR TotalCost = SUM(VP_JobCost[Cost])

-- Next, calculate the total MWdc for that same context
VAR TotalMWdc = SUM('viewpoint vJCJM'[udPVCapMWDC])

-- Convert the MWdc to Wdc
VAR TotalWdc = TotalMWdc * 1000000

-- Safely divide the two values. DIVIDE() automatically handles division-by-zero errors.
RETURN
DIVIDE(TotalCost, TotalWdc)

• Now, use this new [Cost per Wdc] measure in your heatmap matrix or any other visual. It will correctly calculate the ratio based on the context of the visual (for each project, each category, or the grand total) without any errors.

If this explanation and solution resolve your issue, please like and accept the solution.