Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Issues with Merged Column

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!
 
 
Need Help

7 Replies

  • Sandip_Palit's avatar
    Sandip_Palit
    Super User
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous,

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      11 months ago

      Hi Anonymous,

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        11 months ago

        Hi Anonymous,
        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.