Issues with Merged Column

[ef123]

I have a project that is due very soon, and I still can't seem to eliminate this issue, which is impacting many of the visuals in my page. Essentially, the main purpose of this project is to compare project prices measured in $/Wdc in a heatmap matrix. I have a table named VP_JobCost that contains total price values in a column named Cost. However, this table doesn't have an Wdc or Mwdc associated with projects. I located another table (viewpoint vJCJM) that contained watts data in MWdc, and connected this table with the VP_JobCost data based on a Job ID column that appeared in both. However, I discovered that the MWdc column contained very few values, as many had simply not been entered in the table. To solve this problem, I created a new custom table and entered all of the missing MWdc for each project in one column, and the associated job IDs in another. I then merged the new MWdc column with the MWdc column in viewpoint vJCJM. 

I then used the following code to translate MWdc to Wdc, and to then divide the total costs over Wdc: 

Total Wdc3 =

    VAR TotalMW = SUM('viewpoint vJCJM'[udPVCapMWDC])

    VAR TotalW = TotalMW * 1000000

    RETURN TotalW

 The code above is a measure.

Cost_Wdc_VP = VP_JobCost[Cost] / [Total Wdc3]

This code is a custom column located in the VP_JobCost table. For some reason, despite having merged the MWdc columns, the Cost_Wdc_VP column only contain NaN or infinity values.

Please help if you know how to fix this!

[v-mdharahman]

Hi @ef123,

Thanks for reaching out to the Microsoft fabric community forum. It looks like the issue is happening because you’re trying to use a measure ([Total Wdc3]) inside a calculated column. In a calculated column, that measure isn’t getting the same project-level context you see in a visual instead, it’s evaluated row by row at refresh time, which in your case is returning 0 or blank for many rows. That’s why you’re ending up with Infinity or NaN.

You can fix this by skipping the calculated column entirely and doing everything in one measure as already suggested by @Sandip_Palit. That way, Power BI will calculate the $/Wdc based on whatever is being shown in your visual (per project, per category, etc.) instead of trying to store a fixed value for every row.

I would also take a moment to thank @Sandip_Palit and @Ritaf1983, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[v-mdharahman]

Hi @ef123,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[v-mdharahman]

Hi @ef123,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if the issue got sorted.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.

[v-mdharahman]

Hi @ef123,
We noticed there hasn’t been any recent activity on this thread. If you still need support, just drop a reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[Ritaf1983 · Accepted solution]

Hi @ef123 

Please share some data to work with.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Sandip_Palit]

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.

[ef123]

Hello, thank you for the guidance! Just to clarify, how should all of the code you are suggesting I implement be formatted together in one measure? Thank you!