I need a calculated column based on conditions in two columns (Business Unit Number in both tables and L1/Account Categories in 1st table and the second table) which sum and then repeat for several rows before the conditions change and a new sum is repeated for several rows and so on. The L1/Account Categories columns have different names because it's the raw data.
For example, any time ASSETS and 111 appear in the same row, I would want to use those as conditions and with the sum of all of the other matching rows in a new column and the sum would repeat each time both conditions appeared in the same row. Any time P/L and 111 appear in the same row, that would be a sum of all other P/L and 111 appearances in the dataset (about 1000 rows overall)... and so on.
I've tried formulas with DAX using FILTER, SUMX, nested IF statements and also tried the Power Query language among other attempts. Maybe I have to create one or more than one new table? If you need to take a look at a few of my attempts, just let me know.
The top image is how I imagine the output will look in the power query editor and the bottom image is a sample of the source data.
This last pic is from Tableau - I need to make a table in Power BI which essentially a duplicate of this image. The last 2 columns are pulling from different tables.