Calculate Column amount based on condition and row properties

[lemontree]

Hi to everyone,

I am having a challenging issue trying to automate a dataset. My data looks like this:

Item ID   Item Name  Qty   Price

1.1       Item xyz     10        5

1.2       Item fgs     12        10

1.3       Item gfw    10        4

.

.

2.1    item xyt   5        4

2.2   item xyt   6         7

.

.

3.    item k     1       5%

4.   item l       1     3%

so items 3 and 4 are dependent on the the sum of the previous rows. the first step is to calculate qty*price for items in groups 1 and 2 and then the sum those should be multiplied by the respective percentage and give me the results for groups 3 and 4. 

So far I am starting by splitting the item id so that to be able to group and sum by group the calculated amounts but then the problem is that the result for group k should be 5% * ( qty*price for group 1 + qty*price for group 2) etc.

any ideas on how i can automate this one? shall i use some kind of conditional column so that to group 1+2 and then do a second conditional so that to combine them all? 

the additional challenge is that my ending table should include the detailed items for groups 1 and 2 and the values of groups 3 and 4 that will be percentages of the sum of the rows/items above.

thank you in advance for your assistance.

best,

Jim

[v-piga-msft]

Hi @lemontree ,

Firstly, do you want to achieve your output with power query or dax?

If you only want to achieve that with power query, hope @ImkeF  could help.

If you also could achieve that with dax, i still a little confused about your logic and the desired output.

For your data sample, do you want to achieve the group k to be 13.6=5% * ( 210 + 62)?

If not, could you describe your logic in more details and share your desired output so that we could give other ideas?

Best  Regards,

Cherry

[lemontree]

---

@v-piga-msft wrote:

Hi @lemontree ,

Firstly, do you want to achieve your output with power query or dax?

If you only want to achieve that with power query, hope @ImkeF  could help.

If you also could achieve that with dax, i still a little confused about your logic and the desired output.

For your data sample, do you want to achieve the group k to be 13.6=5% * ( 210 + 62)?

If not, could you describe your logic in more details and share your desired output so that we could give other ideas?

Best  Regards,

Cherry

---

Hi @v-piga-msft !

I was thinking to do that in power query but if there is an easier approach through Dax i am fine with this as well. 

Based on the data sample posted the output will be per sub-item, per group and a total one:

Item 1.1 = 10x5 = 50

Item 1.2 = 12x10 =120

Item 1.3 = 10x4 = 40

Item 1 = 50 + 120 + 40 = 210

the same for Item 2 so it will sum up to 62 in total and then 1 + 2 = 272 will be multiplied by 5% to give us item 3 ie 8,16

And then item no 4 should be 3% of items 1, 2 and 3 added together ie 3% x ( 272+8,16) = 14,08

and finaly the total will be the sum of all those.

i hope now it is clear what i am trying to achieve.

thank you in advance for your support!

best,

Jim

[lemontree]

actually the issue is slightly more complicated as once i calculate 3 as the percentage of the sum of groups 1 and 2 then 4 will  be the percentage of the sum of the 1 till 3 and so on...any ideas how to optimize and solve for this? 

currently i am working on a solution where i group 1 and 2 and the group and summarize them and then store the values with drill-down that then i call back and use them with a conditional column. but i am wondering if there is a simpler way to solve it?