Forum Discussion

maek86's avatar
maek86
Frequent Visitor
9 years ago
Solved

Monthly Account Changes

Each month I have a list of accounts that changes. The sales performance for each month must stay with that month's list of accounts. What is the best way to manage this process in power bi? Currently, all my sales are only based off of the current month's list of accounts. While that list of accounts can change month to month.

 

Contract X = Accounts(A,B,C,D,E) when signed. As time progresses the contract cosists of different accounts.

 

Example:

May Sales = Accounts(A,B,C,D,E)

Jun Sales = Accounts (A,B,C)

July Sales = Accounts (A,B,C,D)

and so on. 

 

What are your thoughts on this?

 

  • Anonymous's avatar
    Anonymous
    9 years ago

    I've got a fairly complex answer that you might be able to work from.  I recently posted a solution to a problem here:

    https://community.powerbi.com/t5/Desktop/Get-distinct-Employee/m-p/192034#M84566

     

    In your case, you could modify that to create a table of Accounts by parsing that list of Account names.  Rather then using the count method, you could instead use those to form a SUMX method that iterates over each of the distinct values. 

    Something like this?  (You just need that dummy table i've mentioned, with rows going to the max accounts you could have in a single month)

    SalesRevenue = SUMX(			//This is the row that does the sum of each account
    	SUMMARIZE(					//This will make the distinct values in our column
    		ADDCOLUMNS(				//This creates the calculated column of our Account Names
    			FILTER(				//This cuts down the dummy table to only be the size of the number of Names we have
    				CROSSJOIN(		//This Merges our Dummy Table with the Account Names
    					SUMMARIZE(	//This creates each 'Account Name' row
    						MonthlyTable,
    						MonthlyTable[AccountsList],
    						MonthlyTable[SalesMonth],
    						"NamesCnt",
    						1 + len(MonthlyTable[AccountsList]) - len(SUBSTITUTE(MonthlyTable[AccountsList], "/", ""))	//Count of Slashes
    					),
    					DummyTbl
    				),
    				DummyTbl[Dummy] <= [NamesCnt]
    			),
    			"SubName",
    			PATHITEM(			// This function splits up the Employee names to be placed in each row
    				SUBSTITUTE(MonthlyTable[AccountsList], ",", "|"),
    				DummyTbl[Dummy]
    			)		
    		),
    		[SubName]
    	),
    	[SubName],
    	'SalesTable'[Revenue]
    )

     

    Assumption:  That your accountslist field will be holding the data like "A,B,C" rather than "Accounts(A,B,C"

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    I've got a fairly complex answer that you might be able to work from.  I recently posted a solution to a problem here:

    https://community.powerbi.com/t5/Desktop/Get-distinct-Employee/m-p/192034#M84566

     

    In your case, you could modify that to create a table of Accounts by parsing that list of Account names.  Rather then using the count method, you could instead use those to form a SUMX method that iterates over each of the distinct values. 

    Something like this?  (You just need that dummy table i've mentioned, with rows going to the max accounts you could have in a single month)

    SalesRevenue = SUMX(			//This is the row that does the sum of each account
    	SUMMARIZE(					//This will make the distinct values in our column
    		ADDCOLUMNS(				//This creates the calculated column of our Account Names
    			FILTER(				//This cuts down the dummy table to only be the size of the number of Names we have
    				CROSSJOIN(		//This Merges our Dummy Table with the Account Names
    					SUMMARIZE(	//This creates each 'Account Name' row
    						MonthlyTable,
    						MonthlyTable[AccountsList],
    						MonthlyTable[SalesMonth],
    						"NamesCnt",
    						1 + len(MonthlyTable[AccountsList]) - len(SUBSTITUTE(MonthlyTable[AccountsList], "/", ""))	//Count of Slashes
    					),
    					DummyTbl
    				),
    				DummyTbl[Dummy] <= [NamesCnt]
    			),
    			"SubName",
    			PATHITEM(			// This function splits up the Employee names to be placed in each row
    				SUBSTITUTE(MonthlyTable[AccountsList], ",", "|"),
    				DummyTbl[Dummy]
    			)		
    		),
    		[SubName]
    	),
    	[SubName],
    	'SalesTable'[Revenue]
    )

     

    Assumption:  That your accountslist field will be holding the data like "A,B,C" rather than "Accounts(A,B,C"