How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?

Hi Everyone,

I am at a loss which way I should proceed, and I am hoping someone can lend a hand. I am trying to conduct pareto (80|20) analysis on a data set filtered for either 3,2 or 1 variable based on average monthly volume. Those variables are "Channel," "Product," and "Customer." Each analysis is then used to determine whether one variable is in the top 80% of volume for that subset. Ultimately, all the analysis will end up in Power BI for reporting. I have no problem doing this in excel using a table to run calculations on summaries from a pivot table, but I want the data to both calculate and refresh with each addition to the root dataset. That dataset contains product volume and sales data for between 5 and 7 years. There are about 20 additional non-date variable columns like "Sales Team," "State," and "Distributor" before the 3 groups of variable the analysis is being calculated on. My plan was to do all of this in Power Query and then simply import the data model into Power BI for final reporting. After viewing a bunch of training videos and reading the Q&As on this site, it seems like it’s a better and more efficient idea to do this analysis in DAX either in Power BI or Power Pivot. I thought DAX was going to be much easier than it has turned out to be. For the life of me, I cannot figure out how to translate: filter($x$1:$x$300,((($c$1:$c$300=a1)*($d$1:$d$300=b1))) or if(a2=a1,if(b2=b1,c2+c1,c1),c1)). I can come up with the correct result for one, but if I try to filter or nest 2 non-date variables, I only get errors.

So here are my Qs:
1) Which way would be more efficient? Doing everything in Power Query, doing the calculations in Power Pivot (DAX) or Power BI (DAX)?

2) How does one calculate the % Running Total and then 80 Analysis in each using the most efficient method?

3) Is there a way to simply import the results of calculations done on a pivot table directly into the power pivot data model (or Power Query)? This would be the simplest route for me, and while that seems like something that definitely should be possible, I cannot find anything anywhere that shows how this is done.

a quick note. The formula I am using to calculate whether a variable in the top 80% is to compare the %running total of a row to the % total that is closest to 80%. Except in those instances where I am only assessing one variable (e.g. Customer), the % total closest to 80% is filtered by the remaining variables (e.g. Channel, Product).

I greatly appreciate any suggestions you may have.