Forum Discussion

gauravnarchal's avatar
gauravnarchal
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

How to Calculate Sales Revenue Split

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.

Can I achieve this through a measure?

 

Thanks

 

 

Data Table 

 

Table 1 - ARInvoices

 

InvoiceIDInvoiceNumberAgentDivisonInvoiceDate
10961331009494150001-Jun-21
1095844101495312350003-Jun-21
1095847102041212450005-Jun-21
1095850102587112550007-Jun-21
1096035103133012650009-Jun-21
10958511036789127500011-Jun-21
109626010422481500113-Jun-21
109585210477071500115-Jun-21
10958531053166118500117-Jun-21
10958541058625119500119-Jun-21
10958551064084200500121-Jun-21
10958561069543300500123-Jun-21
10958571075002400500125-Jun-21
109585910804611500027-Jun-21
10958601085920123500029-Jun-21
1095861109137912450001-Jul-21
1095862109683812550003-Jul-21
1095865110229712650005-Jul-21
10958661107756150007-Jul-21
10958671113215150009-Jul-21
109586911186741500011-Jul-21
109587011241331500013-Jul-21
10958711129592123500015-Jul-21
10958721135051124500017-Jul-21
10958741140510125500019-Jul-21
10958751145969126500021-Jul-21
10958761151428123500023-Jul-21
10958771156887124500025-Jul-21
10958791162346125500027-Jul-21
10958831167805126500029-Jul-21
109588511732641500131-Jul-21
10958861178723150012-Aug-21
10958871184182150014-Aug-21
1095889118964111850016-Aug-21
1095890119510011950018-Aug-21
10958911200559200500110-Aug-21
10958921206018300500112-Aug-21
10958931211477400500114-Aug-21
109605912169361500116-Aug-21
109589412223951500118-Aug-21
109601412278541500120-Aug-21
10958951233313118500122-Aug-21
10959001238772119500124-Aug-21
10958971244231200500126-Aug-21
10958981249690300500128-Aug-21
10959021255149400500130-Aug-21

 

Table 2 ARInvoicesDetail

 

InvoiceIDSale
10961331750.88
1095844740
10958475140
1095850120
10960353518.69
1095851250
1096260490
10958523360
10958531920
10958541980
10958551660
10958564000
10958571670
10958592014
1095860640
10958611670
1095862770
10958652580
10958661960
10958671980
10958695060
1095870970
10958711890
10958721190
10958741380
10958751900
10958761380
10958772010
1095879270
10958832060
10958853540
1095886850
1095887890
1095889150
1095890200
1095891100
10958924040
10958933710
1096059370
1095894660
10960145410
10958951100
10959002370
10958975720
10958981200
10959022166

 

Current result in table format

 

Division5000
AgentSale
113734.88
1234650
12410010
1252540
12610058.69
127250
Grand Total41243.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)

 

Division5000
AgentSale
1238083.72
12413443.72
1255973.72
12613492.41
127250
Grand Total41243.57
Need Help
Show and Tell
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Here are a couple of different approaches:

    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] ) )
        )
    )

     

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    Here are a couple of different approaches:

    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] ) )
        )
    )

     

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        I'm guessing that means your Agent column is a text type. Either covert that to a whole number data type or else adjust the measures to put quotes around the agent values (i.e. "1", "123", etc instead of 1, 123).