PowerPivot/DAX – 将一些群组/通道分配给其他群组/通道

huangapple go评论100阅读模式
英文:

PowerPivot/DAX - distribution of some groups/channels to other ones

问题

我有以下的销售表格(来自数据立方体):

  1. 渠道 | 中心 | 数量
  2. =========+========+=====
  3. D2D | H100 | 100
  4. D2D | H200 | 400
  5. Ext | T100 | 320
  6. Ext | T200 | 280
  7. Ch1 | C100 | 540
  8. Ch1 | C400 | 320
  9. Ch2 | C400 | 240
  10. Ch3 | C300 | 200

我需要汇总/透视通过销售渠道出售的数量,但需要应用一些规则 - 通过D2D和Ext渠道出售的数量应分配给Ch1和Ch2:

分配规则 1)将在H100中心销售的“D2D”渠道数量的50%添加到Ch1渠道,50%添加到Ch2渠道,H200中心也适用相同的规则
分配规则 2)所有“Ext”渠道数量(不考虑中心)的80%应该添加到Ch1渠道,20%添加到Ch2渠道

因此,结果表格应如下所示:

  1. 渠道 | 数量
  2. =========+=====
  3. Ch1 | 1590 <-- 540 + 320 + 0.5*100 + 0.5*400 + 0.8*(320+280)
  4. Ch2 | 610 <-- 240 + 0.5*100 + 0.5*400 + 0.2*(320+280)
  5. Ch3 | 200

如何在PowerPivot/DAX中执行此操作?

英文:

I've got the following sales table (from datacube):

  1. Channel | Center | Qty
  2. =========+========+=====
  3. D2D | H100 | 100
  4. D2D | H200 | 400
  5. Ext | T100 | 320
  6. Ext | T200 | 280
  7. Ch1 | C100 | 540
  8. Ch1 | C400 | 320
  9. Ch2 | C400 | 240
  10. Ch3 | C300 | 200

I need to sum/pivot quantities sold through sales channels but some rules should be applied - quantities sold through D2D and Ext channels should be distributed to Ch1 and Ch2:

distribution rule 1) 50% of D2D channel quantities sold in H100 center should be added to Ch1 channel, and 50% to Ch2 channel, the same applies for H200 center
distribution rule 2) 80% of all Ext channel quantities (regardless of center) should be added to Ch1 channel and 20% to Ch2 channel

So the resulting table should look like this:

  1. Channel | Qty
  2. =========+=====
  3. Ch1 | 1590 <-- 540 + 320 + 0.5*100 + 0.5*400 + 0.8*(320+280)
  4. Ch2 | 610 <-- 240 + 0.5*100 + 0.5*400 + 0.2*(320+280)
  5. Ch3 | 200

How to do this in PowerPivot/DAX?

答案1

得分: 1

PowerBI/DAX中,您可以使用以下公式来创建新列:

  1. Ch1 = if(AND([Channel]="D2D",OR ([Center]="H100", [Center]="H200")),0.5*[Qty],
  2. if([Channel]="Ext",0.8*[Qty],if([Channel]="Ch1",[Qty],0)))
  3. Ch2 = if(AND([Channel]="D2D",OR ([Center]="H100", [Center]="H200")),0.5*[Qty],
  4. if([Channel]="Ext",0.2*[Qty],if([Channel]="Ch2",[Qty],0)))
  5. Ch3 = if([Channel]="Ch3",[Qty],0)

接下来只是决定如何显示结果的问题。例如,在多行卡上可能是这样的:

PowerPivot/DAX – 将一些群组/通道分配给其他群组/通道

当然,在Power BI中有许多呈现数据的方式。

在Power Pivot中,您可以使用相同的DAX公式添加列,然后在Excel中创建一个数据透视表。您可能需要转置该表以复制您上面的输出。

或者您可以在Power Query中使用M代码来完成相同的任务。

英文:

In PowerBI/DAX, you can use the following formulas for new Columns

  1. Ch1 = if(AND([Channel]="D2D",OR ([Center]="H100", [Center]="H200")),0.5*[Qty],
  2. if([Channel]="Ext",0.8*[Qty],if([Channel]="Ch1",[Qty],0)))
  3. Ch2 = if(AND([Channel]="D2D",OR ([Center]="H100", [Center]="H200")),0.5*[Qty],
  4. if([Channel]="Ext",0.2*[Qty],if([Channel]="Ch2",[Qty],0)))
  5. Ch3 = if([Channel]="Ch3",[Qty],0)

Then it is just a matter of deciding how to display the results. eg on a Multi-Row Card it might be:

PowerPivot/DAX – 将一些群组/通道分配给其他群组/通道

But, of course, in Power BI there are many ways to present the data.

In Power Pivot you can use the same DAX formulas to add the columns, then create a Pivot Table in Excel. You may have to transpose that table to duplicate your output above.

Or you can accomplish the same thing in Power Query using M Code

huangapple
  • 本文由 发表于 2023年7月14日 05:38:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/76683397.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定