Search code examples
excelpowerbidaxpowerpivot

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

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

 Channel | Center | Qty
=========+========+=====
 D2D     | H100   | 100
 D2D     | H200   | 400
 Ext     | T100   | 320
 Ext     | T200   | 280
 Ch1     | C100   | 540
 Ch1     | C400   | 320
 Ch2     | C400   | 240
 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:

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

How to do this in PowerPivot/DAX?

Solution

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

    Ch1 = if(AND([Channel]="D2D",OR ([Center]="H100", [Center]="H200")),0.5*[Qty],
if([Channel]="Ext",0.8*[Qty],if([Channel]="Ch1",[Qty],0)))

Ch2 = if(AND([Channel]="D2D",OR ([Center]="H100", [Center]="H200")),0.5*[Qty],
if([Channel]="Ext",0.2*[Qty],if([Channel]="Ch2",[Qty],0)))

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:

    enter image description here

    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