Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
@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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |