Summarizing data similar to "For Each"

[10500438]

I have a table with 7 columns 

year, period, order, item, mill, quantity (whole number value), yield (this is a percentage value)

in my pivot table, if I have the columns year, period, order, item then the Yield column is the product of the yield for each mill. This is a simple dax formula, =product(yield).

if I decide I want to remove columns and only have year and period then the formula changes.

First Step

I have to find the product of the Yield for each year, period, order, item called "Prod Yield" and the sum of the quantity for each year, period, order, item called "Order Qty"

Second Step

I divide Order Qty by Prod Yield to get a new value called "Yield Req" for each year, period, order, item

Third step

Once this new value called "Yield Req" is calculated, I can divide the Sum of Order Qty by the Sum of Yield Req to get a new Total Yield value for each year period.

how do i get the calculated column to iterate through each row and hold the calculation from step 2 to arrive at step 3, no matter if the year, period, order, item is present in the visual?

[Greg_Deckler]

@10500438 - Sample data would help tremendously. Meanwhile I wrote some FOR and WHILE equivalents in DAX that may help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/For-Loop/m-p/637531#M319

https://community.powerbi.com/t5/Quick-Measures-Gallery/While-Loop/m-p/637535#M320

[10500438]

Greg Can i email the file to you?

I read your articles and dozen more related to for each, but I wonder if summarizecolumns is what i am looking for. 

In power query in excel i have already grouped by to reduce the rows but with 5 quarters of data, I am up to 700mb file size already and its extremely slow. I was trying to prevent doing some work in excel and some in power bi, but the file source that feeds my pbix file is excel because there is no direct connection to SAP for this data.

Each unique combination has various mill areas but doesn't necessarily have all mill area combinations, to overcome this i have to query the data for missing combinations and include them so that my calculations are accurate.

If you could see my example it would make more sense, because its multiplication a value or a placeholder must be present or the calculations are not accurate. I have been spinning my wheels for months with this issue and including all combinations was the only way to overcome it (based on my knowledge) but it made the file large and slow.

I would appreciate any help you can give, thank you again for your time. I should probably mention I am not a programmer but I can usually figure out enough to get by.

[daxer-almighty]

By the way, SUMMARIZECOLUMNS is not a function that you can use in measures. It only works for querying, so you can use it in a Power BI file to create calculated tables.

[10500438]

Thank You Daxter, I wasn't sure how to upload the file from excel to this community.

https://arcelormittal-my.sharepoint.com/:x:/p/amy_deloach/EUEzKPT91dpOhx5e9GbkKEcB4fqpElYf1NxKadjx-w...

Here is the link to the file on my onedrive.

[10500438]

@Greg_Deckler  @daxer-almighty 

Any suggestions? I upload a link to a file example. Thanks!~

[daxer-almighty]

@10500438 

Why don't you create a simple small file (PBI or Excel) with some example data in it and a good explanation in there of what you're trying to achieve and post a OneDrive or Google Drive link to it? You really don't have to give the full file (700MB?) away.

[10500438]

 Is there a way to upload an excel file that has an example?