TRansforming Excel Fornula in Power BI

[rehansaeed2468]

I have 2 tables Table A and Table B, I am looking for the Desired Results tab Numerator and Denominator calculation in Power BI.  This will require creating unique index field in the table. Unique Index should be on ProductID and Order Date. Also the Index have to created as a measure cuz it should respoect the filters so It has to be done in the Reports View.

Sample Excel file link Below.

https://docs.google.com/spreadsheets/d/1IVujWmZuJfPLGEFzLywzE4eCqiTiTUL3/edit?usp=drive_link&ouid=10...

[v-sshirivolu · Accepted solution]

Hi @rehansaeed2468 ,

Thanks for sharing the sample Excel file and explaining the requirement clearly. I have recreated the same scenario in Power BI and attached the PBIX file for your reference.

In this case, the two tables should not be merged and should stay connected only through Product ID. To get the same result as Excel SUMPRODUCT, the row index has to be created as a measure, not as a calculated column, so that it changes properly based on slicers and user selections. Since multiple rows can have the same Order Date, a tie-breaker using Product ID is also required to keep the exact row order like Excel.

The Numerator and Denominator are calculated using measures with SUMX, and the calculation is based on the Row Index, not on Order Date. This ensures the values are calculated row by row, exactly like Excel, and they update dynamically when filters are applied. With this approach, the Power BI output now matches the Desired Results shown in Excel

Please check the below attached PBIX file to see the complete working solution and validation. Let me know if you need any help further.

[v-sshirivolu]

Hi @rehansaeed2468 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @rehansaeed2468 ,
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

[rehansaeed2468]

Is there a way to do this without merging the tables. Please check my pbix file located at the below link. I am able to calculate the denominator without mergingthe tables but Numerator is the one giving me hard time.

https://s3-project-rehan.s3.us-east-2.amazonaws.com/sample_PBIX.pbix

[rehansaeed2468]

Cant merge the 2 tables they have to be associated on Product ID

[ryan_mayu]

@rehansaeed2468 

you need to merge two table ( I skipped that step) and create some calculated columns

pls see the attachment below

[rehansaeed2468]

I updated the post with the link to the my pbix file. Like I said I can't merge tables and the D3 have to be a measutre not a column cuz it should be able to update based on the user selections.