Forum Discussion
Excel Calc in Power BI
Hi Rehansaeed2024 ,
Thanks for confirming that the results are matching in the reference PBIX. This confirms that the Excel decayed SUMPRODUCT logic is correctly implemented in Power BI using DAX.The issues you are facing in your actual PBIX are not due to the formula, but mainly because of model structure and data size. The circular dependency error usually comes when a measure like Numerator_Decayed is used directly or indirectly inside a calculated column, or when the Row Index itself is created using measures. In the working PBIX, the Row Index is a simple calculated column, and both Numerator and Denominator are measures only, which avoids this issue.
The “Query exceeded available resources” message happens when the SUMX + FILTER logic runs over a very large or highly detailed dataset. In such cases, the data grain or Row Index scope needs to be reduced to improve performance.
When you see 0.00 values for smaller datasets, it usually means the Row Index is not stable or the table is not sorted in the same order as Excel before assigning the index. Since Excel’s ROW() depends on position, sorting is very important.
Please use the attached PBIX as a reference for how the Row Index and measures should be structured. Once the same separation and sorting are applied in your actual model, the Numerator_Decayed will behave exactly the same as Excel.
Hi Rehansaeed2024 ,
I've attached a PBIX file to help you verify the calculation. It includes a synced Row Index so Excel and Power BI follow the same row order, the full NumeratorDecayed measure, and a table showing the correct results. Once you open the file and view the sorted table, you’ll see the Numerator matches Excel because the decay logic depends on row order.
Please find below attached .pbix file for your reference.
Helper IDenominator is working as expected but the Numerator_Decayed is giving "Query Exceeded Availabe Resources"
- rehansaeed2468
and If I am trying a smaller data set I am getting 0.00 for all rows
Hi Rehansaeed2024 ,
Thanks for confirming that the results are matching in the reference PBIX. This confirms that the Excel decayed SUMPRODUCT logic is correctly implemented in Power BI using DAX.The issues you are facing in your actual PBIX are not due to the formula, but mainly because of model structure and data size. The circular dependency error usually comes when a measure like Numerator_Decayed is used directly or indirectly inside a calculated column, or when the Row Index itself is created using measures. In the working PBIX, the Row Index is a simple calculated column, and both Numerator and Denominator are measures only, which avoids this issue.
The “Query exceeded available resources” message happens when the SUMX + FILTER logic runs over a very large or highly detailed dataset. In such cases, the data grain or Row Index scope needs to be reduced to improve performance.
When you see 0.00 values for smaller datasets, it usually means the Row Index is not stable or the table is not sorted in the same order as Excel before assigning the index. Since Excel’s ROW() depends on position, sorting is very important.
Please use the attached PBIX as a reference for how the Row Index and measures should be structured. Once the same separation and sorting are applied in your actual model, the Numerator_Decayed will behave exactly the same as Excel.
Hi Rehansaeed2024 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.