Is it possible to create calculated tables by splitting rows in DAX?

[JerryZhou]

Hi friends, 

I am looking for some ideas/help on how I can fulfill the following reporting requirement, which basically involves manipulating a table into a more workable format. I know there's probably an easier solution in Power Query, but I am trying to do this in DAX because the existing table was created through DAX. 

Here is an example to demonstrate the rules:

Below is a list of 4 projects that was sold in the first quarter of the year, and each project can be managed by one or two sales persons.

1. If a project has 2 salespersons, each salesperson earns half of the revenue amount.
   • (i.e. 50% goes to salesperson A, 50% goes to salesperson B)
2. If the project has a lead time of more than 40 weeks, the revenue amount needs to be split between the quarter in which it was sold and the following quarter.
   • (i.e. For a large project sold in Q1, 50% goes under Q1, 50% goes under Q2)
3. The two rules can stack on top of each other, in which case if a project has 2 sales persons and a long lead time, 25% of the revenue will go to each sales person under each of Q1 and Q2. 
   • (i.e. 25% goes to salesperson A under Q1, 25% goes to salesperson B under Q1, 25% goes to salesperson A under Q2, 25% goes to salesperson B under Q2) 

Existing table: 

Quarter	Project	Revenue	Salesperson	Secondary Salesperson	Lead Time
FY2021Q1	P1	$1,000	A		20
FY2021Q1	P2	$1,000	B		50
FY2021Q1	P3	$1,000	C	D	20
FY2021Q1	P4	$1,000	E	F	50

Desired format: 

Quarter	Project	Revenue	Salesperson
FY2021Q1	P1	$1,000	A
FY2021Q1	P2	$500	B
FY2021Q2	P2	$500	B
FY2021Q1	P3	$500	C
FY2021Q1	P3	$500	D
FY2021Q1	P4	$250	E
FY2021Q1	P4	$250	F
FY2021Q2	P4	$250	E
FY2021Q2	P4	$250	F

Can anyone please help?

I'm not looking for a full solution but I would really appreciate if you can give any direction or mention some functions that I should use to make such transformation. 

Thanks!

Jerry

[Anonymous · Accepted solution]

Hey, I started off with the "Why not?" question and worked on it step by step, starting from your sample data in a table named "Existing Table". I have not thought about the right method, shortest method, optimized method, or anything like that. Just built the code step by step, but got the result.

Existing Table

Output

The DAX code for this calculated table is given below, it is quite large, but I have given comments in each step so that you will be able to understand what each step does.

ProjectTable = 
-- Get a list of projects
VAR ListOfProjects = ALLSELECTED(ExistingTable[Project],ExistingTable[Lead Time],ExistingTable[Revenue])
-- Crossjoin the list of projects with another table with one-column and two-rows that contains the list of 2 sales persons (the field name of the new table will be "Value")
VAR LOP_WS =
    GENERATE(
        ListOfProjects,
        VAR CP = [Project]
        VAR SP1 = LOOKUPVALUE(ExistingTable[Salesperson],ExistingTable[Project],CP)
        VAR SP2 = LOOKUPVALUE(ExistingTable[Secondary Salesperson],ExistingTable[Project],CP)
        VAR RN = IF(ISBLANK(SP2),BLANK(),2)
        VAR LOS = { {SP1},{SP2} }
        RETURN
        LOS
    )
-- Rename the "Value" field to "Sales Person" and also filter the blanks out.
VAR X = SELECTCOLUMNS(
FILTER(LOP_WS,[Value]<>""),
"Project",[Project],
"Revenue",[Revenue],
"Lead Time",[Lead Time],
"Sales Person",[Value]
)
--Add an additional row for all projects with more than 50 as the lead time. And an additional column to identify the extra rows. 
--The new field will have 3 values, 0 in the existing rows. 1 in the new field if the project has leadtime>=50, blank() in the new field if the project has <50 lead time.
VAR LOP_WS2 =
    GENERATE(
        X,
        VAR LT = IF([Lead Time]>=50,1,BLANK())
        VAR T = {{0},{LT}}
        return
        T
    )
--Filter out the blanks in the new field and also add the starting quarter of the project as "Start Q"
VAR Output1 = ADDCOLUMNS(FILTER(LOP_WS2,NOT(ISBLANK([Value]))),"Start Q",LOOKUPVALUE(ExistingTable[Quarter],ExistingTable[Project],[Project]))

-- Add a new field named "Quarter" with value "Start Q" in the "Value" field with 0 and Start Q + 1 in rows with "value" field having value 1. Blanks will not be there as we have filtered it out.
-- LEFT(), LEN(), MID(), VALUE() etc... are basic text handling functions used to generate the next quarter's value from a string like "FY2021Q1"

VAR Output2 = 
    ADDCOLUMNS(
        Output1,
        "Quarter",
        VAR V = [Value]
        VAR SQ = [Start Q]
        VAR NQ = 
            IF(
                VALUE(RIGHT(SQ,1))<4,
                LEFT(SQ,LEN(SQ)-1)&(value(RIGHT(SQ,1))+1),
                LEFT(SQ,2)&(VALUE(MID(SQ,3,4))+1)&"Q1"
                )
        VAR Q = 
            IF(V=0,SQ,NQ)
        RETURN
        Q
    )
-- Add a temporary field called "Revenue 2" and split the revenue between the quarters and the number of salespeople. The cardinality of each project in the table will tell us the denominator.
VAR Output3 =
    ADDCOLUMNS(
        Output2,
        "Revenue 2",
        VAR Proj = [Project]
        VAR ProjCount = COUNTROWS(FILTER(Output2,[Project]=Proj))
        VAR Rev = [Revenue]
        VAR Rev2 = DIVIDE(Rev,ProjCount,0)
        RETURN
        Rev2
    )
--Discard unnecesary fields and choose only relevant fields with correct field names.
VAR Result = 
    SELECTCOLUMNS(
        Output3,
        "Quarter",[Quarter],
        "Project",[Project],
        "Sales Person",[Sales Person],
        "Revenue",[Revenue 2]
    )
RETURN
-- Return the result.
Result

[JerryZhou]

Hi there, 

Thank you so much for taking the time to help me solve this puzzle. I spent some time trying to understand the thought process behind your solution, and it worked magical. 🙂 

Before seeing your solution I wasn't even sure if this was possible. Thank you.