How to Calculate Sales Revenue Split

[gauravnarchal]

Hello - I want to check if there is a possibility to

1. Divide the total sale amount of agent 1 in the division 5000 by 4  (Divide by 4)
1. and add each it to the total sale amount to the agents 123 & 124 & 125 & 126. For all other agents, it should be the actual sale amount.

Column "Agent" in Table 1 - ARInvoices is in TEXT format and cannot be changed to number format due to other complexities. 

Below 2 methods works when I change the Agent column to number but do not work when the format is TEXT.

If I put quotes around the agent values (i.e. "1", "123", etc instead of 1, 123) it still does not work.

Any suggestions?

Split1 = 
VAR Agent1Sales = CALCULATE ( SUM ( ARInvoicesDetail[Sale] ), ARInvoices[Agent] = 1 )
VAR OtherAgentSales = CALCULATE ( SUM ( ARInvoicesDetail[Sale] ), KEEPFILTERS ( ARInvoices[Agent] <> 1 ) )
VAR Agents12XCount = COUNTROWS ( INTERSECT ( VALUES ( ARInvoices[Agent] ), { 123, 124, 125, 126} ) )
RETURN
IF (
    SELECTEDVALUE ( ARInvoices[Agent] ) = 1, BLANK(),
    OtherAgentSales + Agent1Sales * Agents12XCount / 4
)

Split2 = 
VAR Agent1Sales = CALCULATE ( SUM ( ARInvoicesDetail[Sale] ), ARInvoices[Agent] = 1 )
RETURN
    SUMX (
        VALUES ( ARInvoices[Agent] ),
        SWITCH (
            TRUE (),
            CALCULATE ( SELECTEDVALUE ( ARInvoices[Agent] ) ) = 1, BLANK (),
            ARInvoices[Agent] IN { 123, 124, 125, 126 },
            CALCULATE ( SUM ( ARInvoicesDetail[Sale] ) ) + Agent1Sales / 4,
            CALCULATE ( SUM ( ARInvoicesDetail[Sale] ) )
        )
    )

Data Table 

Table 1 - ARInvoices

Note - "Agent" column is in TEXT format and cannot be changed to number format due to other complexities.

InvoiceID	InvoiceNumber	Agent	Divison	InvoiceDate
1096133	1009494	1	5000	1-Jun-21
1095844	1014953	123	5000	3-Jun-21
1095847	1020412	124	5000	5-Jun-21
1095850	1025871	125	5000	7-Jun-21
1096035	1031330	126	5000	9-Jun-21
1095851	1036789	127	5000	11-Jun-21
1096260	1042248	1	5001	13-Jun-21
1095852	1047707	1	5001	15-Jun-21
1095853	1053166	118	5001	17-Jun-21
1095854	1058625	119	5001	19-Jun-21
1095855	1064084	200	5001	21-Jun-21
1095856	1069543	300	5001	23-Jun-21
1095857	1075002	400	5001	25-Jun-21
1095859	1080461	1	5000	27-Jun-21
1095860	1085920	123	5000	29-Jun-21
1095861	1091379	124	5000	1-Jul-21
1095862	1096838	125	5000	3-Jul-21
1095865	1102297	126	5000	5-Jul-21
1095866	1107756	1	5000	7-Jul-21
1095867	1113215	1	5000	9-Jul-21
1095869	1118674	1	5000	11-Jul-21
1095870	1124133	1	5000	13-Jul-21
1095871	1129592	123	5000	15-Jul-21
1095872	1135051	124	5000	17-Jul-21
1095874	1140510	125	5000	19-Jul-21
1095875	1145969	126	5000	21-Jul-21
1095876	1151428	123	5000	23-Jul-21
1095877	1156887	124	5000	25-Jul-21
1095879	1162346	125	5000	27-Jul-21
1095883	1167805	126	5000	29-Jul-21
1095885	1173264	1	5001	31-Jul-21
1095886	1178723	1	5001	2-Aug-21
1095887	1184182	1	5001	4-Aug-21
1095889	1189641	118	5001	6-Aug-21
1095890	1195100	119	5001	8-Aug-21
1095891	1200559	200	5001	10-Aug-21
1095892	1206018	300	5001	12-Aug-21
1095893	1211477	400	5001	14-Aug-21
1096059	1216936	1	5001	16-Aug-21
1095894	1222395	1	5001	18-Aug-21
1096014	1227854	1	5001	20-Aug-21
1095895	1233313	118	5001	22-Aug-21
1095900	1238772	119	5001	24-Aug-21
1095897	1244231	200	5001	26-Aug-21
1095898	1249690	300	5001	28-Aug-21
1095902	1255149	400	5001	30-Aug-21

Table 2 ARInvoicesDetail

InvoiceID	Sale
1096133	1750.88
1095844	740
1095847	5140
1095850	120
1096035	3518.69
1095851	250
1096260	490
1095852	3360
1095853	1920
1095854	1980
1095855	1660
1095856	4000
1095857	1670
1095859	2014
1095860	640
1095861	1670
1095862	770
1095865	2580
1095866	1960
1095867	1980
1095869	5060
1095870	970
1095871	1890
1095872	1190
1095874	1380
1095875	1900
1095876	1380
1095877	2010
1095879	270
1095883	2060
1095885	3540
1095886	850
1095887	890
1095889	150
1095890	200
1095891	100
1095892	4040
1095893	3710
1096059	370
1095894	660
1096014	5410
1095895	1100
1095900	2370
1095897	5720
1095898	1200
1095902	2166

Current result in table format

Division	5000
Agent	Sale
1	13734.88
123	4650
124	10010
125	2540
126	10058.69
127	250
Grand Total	41243.57

Result

Agent 1 total sale - 13734.88 / 4 = 3433.72 (3433.72 should be added to agent 123, 124, 125, 126 of division 5000)

Division	5000
Agent	Sale
123	8083.72
124	13443.72
125	5973.72
126	13492.41
127	250
Grand Total	41243.57

[v-luwang-msft]

Hi @gauravnarchal ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[gauravnarchal]

Hi @v-luwang-msft  The measure is now working. The only issue I am having is when I add the name to the table, the split measure does not work. What could be the reason?

[AlexisOlson]

Your code looks like it works if you change all of the IDs to text:

I'd advise against hard-coding the non-"1" IDs though.

[gauravnarchal]

@AlexisOlson - The measure is now working. The only issue I am having is when I add the name to the table, the split measure does not work. What could be the reason?

[AlexisOlson]

Probably the additional filter context. Try adding ", ALL ( ARInvoices ) " inside the first CALCULATE.

[gauravnarchal]

@AlexisOlson - Still the same issue.  When I am adding any other column, the split formula doesn't work. What could be the reason?